I've been thinking about my database indexes lately, in the past I just kind of non-chalantly threw them in as an afterthought, and never really put much thought into if they are correct or even helping. I've read conflicting information, some say that more indexes are better and others that too many indexes are bad, so I'm hoping to g...
I am designing a data model for tourism-site. I have a table for places (countries, resorts, hotels) and a table for tours. The tables are pretty different in fields so the cant be merged into one. Both of them have usual auto_increment as id. Places and tours have photos so there is a third table for photos. This table has 'parent' fiel...
I have a site that is essentially a collection of links. A mysql database which stores users, links and votes. My links table has two foreign keys, user_id and vote_id. When a link is inserted into the database it will start with one vote so that means I need to insert a row into the votes table but they essentially depend on one anot...
I'm creating a simple set of tables to store a number of lists. The first table, describes the lists that I have and second describes items on those lists - the tables are defined like so: CREATE TABLE `listman_list` ( `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `title` varchar(100) NOT NULL, `description` varchar(1000...
I have the following parent <-> child datamodel: (almost every line is a table, indented means child-of) consumerGoods food meat item fruit item vegetable item The child-items of meat, fruit and vegetables are in the same table (named items) because they have identical a...
I am putting together a schema for a database. The goal of the database is to track applications in our department. I have a repeated problem that I am trying to solve. For example, I have an "Applications" table. I want to keep track if any application uses a database or a bug tracking system so right now I have fields in the Applica...
We are trying to track our applications in our department and our unit test usage so I have created a database to keep track of this. I have an Applications table and at first, I created a UnitTests column in the Applications table but I just realized that by simply keeping this up to date it will overwrite the history of unit test for ...
Well, here is a small problem. I am to design a web application database to use what is, basically, one spoken language. However this language has two alphabets (Latin & Cyrillic) and three spoken variants that actually have only some minor differences (I think there is several hundred words that are slightly different). To make matters...
I need to add a delete flag column to all 40 user tables in a database. I could write a script to loop through sys.tables, but I thought I'd check and see if anyone has either a better solution, or pre-created sql for this scenario. ...
i have an Applications table and a Resources tables. Since many resources can work on a single application and many applications can be worked on by a single resource, i created a 3rd table: ApplicationResources: - ID (int) - ApplicationID (int) - ResourceID (int) - PctOfTime (float) is there anyway in the database itself that i...
My application is basically a way for groups to manage content. There will be 3 different levels of users: Website Administrators: a few people who can do everything. Group administrators: privilege to upload files, edit calendars, and manage members, but only for their group. Members: can see content for the groups that they are memb...
I have to store a set of related keywords inside a database. As of now, I am thinking of using the following: To store the keywords themselves: CREATE TABLE keywords( id int(11) AUTO_INCREMENT PRIMARY KEY, word VARCHAR(255) ); To store the relations (stores the ids of the related keywords): CREATE TABLE relatedkeywords( id ...
Firstly this site is based on guests who are interested in selling their items to the site owner, so this isn't auction based like Ebay. Visitors basically would enter in details of an item ( tech gadget ) they'd like to sell, including condition, are given a quote on how much the item is worth, then fill in address info so the owner sh...
I'm not sure how best to phrase the question, but essentially I have a table of contacts, and instead of doing the typical -- a contact has a reference to a table with spouse information, and a table with children, I want each of those people to be a contact, but then define a relationship between those contacts (brother, sister, child, ...
I have a table with a list of user names, how do I implement a custom sort order for the following scenarios. create table User (name varchar(255)) e.g. User {John, Sam, Mike} the order could be re-arranged in the UI using the Up / Down buttons to {Sam, John, Mike} or {Sam, Mike, John} Do I need to add a separate column to the table...
I know of two ways to delete data from a database table DELETE it forever Use a flag like isActive/isDeleted Now the problem with isActive is that I have to track everywhere in my SQL queries that whether the record is active or not. Using DELETE however gets rid of the data forever. What would be the best way to backup this data? ...
Hi there, I'm having some thoughts about proper building my app and provide a good and consistent API for it but now I'm having some doubts about the user/accounts model. It's funny but if you consider some apps you will see that they treat you like user but when editing your details your are redirect to account. One good example of th...
I'm building a Wine review site and have run into a database design problem I was hoping someone could help me with. The basic premise of the site is that users will log in a leave reviews of wines they have tested. users wines reviews A user has many reviews, review belongs to user and review belongs to wine. This much is easy. ...
In a specific table I have a SortOrder integer field that tells my page in which order to display the data. There are sets of data in the this field (based on a CategoryID field), and each set will have its own ordering. Users can add/remove/update records in this table. My question is what is the best way to manage this SortOrder fie...
i have a webbased tracking application and i am storing data in SQL server. I am tracking which locations i have deployed applications. The web interface is a dropdown combo and I have a varchar(100) field right now. I have the user select a dropdown list: Global America Europe Asia but now i have applications that are stored in m...