MYSQL :: Db Design

DB Design
I don't know why i am haveing a tough time on this one. i could just do it, but i want to do it the best way.

Its for cars; make, model, engine,

I'm just not convinced this is the way I should do it, because I also want to inlcude the years for the cars, and that vairies per model / engine combination

I was going to have a cars table that would have the following, but need help with getting the years in there also. Should Each row in the CARS table have a YEAR field, so a 1993 VW Corrado SLC and a 1994 VW Corrado SLC would have their own rows, even though they are basicly identica cars? or should I do a single row with a from and to year field?

i am leaning towards the one row for every configuration, since the data is small (mostly foriegn keys) and it would be precise and easy to look up between years, by model, etc.

Which Design Would Be Correct?
I have registered users, and these users can belong to a group. I'm trying to decide whether or not i should keep this group_id in the users table, or to create a cross referencing table and just keep the user_id and group_id in there. the cross reference table will allow for users to be associated with more than one group. and just keeping the group_id in my users table will allow me to avoid a few joins in some queries but only allow one user per group... any opinions on this matter? after typing this i feel as though creating the referencing table would be the better idea.. but i still would love to hear other opinions.

Table Design
I am designing a table for "friend relationships".

I have three possible solutions:
a)
user_id (int)
friend_id (int)
created_at (timestamp)

This will require 2 rows per friendship.
b)
user_id (int)
friend_id (int)
user_id2 (int)
friend_id2 (int)
created_at (timestamp)
This requires only 1 row, but 2 indices.
c)
user_id (int)
friends (text)

This contains a comma delimited lists of all friend_ids.

I am leaning towards option 3, ofcourse, adding & deleting friends will be a bit slower, but the bulk of the load is on reading of friend data anyways.

However I have a question if this would be slower:

QUERY 1
select items.* from items inner join friends ON friends.friend_id = items.user_id WHERE friends.user_id = [my_user_id]

QUERY 2
select items.* from items where user_id IN (select friends from network where user_id = [my_user_id]

QUERY 3
$friends (variable) = select friends from network where user_id = [my_user_id]

select items.* from items where user_id IN ($friends)

Query 3 requires 2 queries.

1. Which of these would be faster?
2. And would you still choose the solution you chose in 1. if I had to use the friend userlist on multiple occasions on the same page?

(select from groups, select from bulletins, select from items, select from users)

How To Design Database
I want to build a database for menu driven.
like
file
--new
--- html
--- php
--open
--- html
--- php
--save
edit
--cut
--copy
--paste
and like that.

So,please help me how to start with designing the database as it can have n no. of levels.

DB Design Question...
I am almost done building a LAN-based Point-of-Sale system. I am somewhat new to mySQL, although I have worked with it for a few years now. I've only used mySQL for simple things like storing images and contact info for people that use my web-based contact forms. Now I want to do something a little more in-depth, and I need some suggestions.

I want to be able to not only insert each invoice into the DB, but I also want to do so in a way that would allow me to provide various reports in a logical wy. As it stands now, I have 1 table that I call "invoices", where I store all the info that is found on a completed invoice: customer's information, date, sale type, and sale details, which is a semi-colon delimited list of all the items, quantities, and prices for everything they bought.

Now, after building that table and playing with it a little, it seems rather cumbersome to work with. I will have to do a lot of parsing in order to make reports like 10 Most Popular Items, Total Profit for the last week, Total Item Cost of all tickets for month of Nov, for example, since all of those details are in a list in one column.

How would you guys build your table/tables to make such data storage and it's subsequent access more logical with less overhead? A seperate customers table linked with an items table? Or would you do something different?

How Best To Design DB On MySQL 5.0 So That ..
I couldn't find this by a forum search.

On my local PC, I have MYSQL 5.x and was wondering how best to design certain fields in my table that MySQl 4.1 ( my hosting service ) won't complain about.

For instance, I noticed that when I pushed a PHP app from my local PC, up to my Host provider account, things failed because MySQl 4.1 didn't like 1024 character varchar's.

Is there a guide somewhere that would make it easy to design an downward-compatible DB ?

OO Design With MySQL
I know how to pull data from MySQL into an ASP.NET page using MySql.Data.MySqlClient and its connection, command, and data reader classes. Now I would like to kick it up a notch and start working in OO design principles into my code. The tutorials I've seen seem to be geared toward SQL Server and so I'm spinning my wheels.

Can anybody help me find a good tutorial (or a good book) that spells out in detail how to get the most out of MySQL in an ASP.NET environment. I'm especially interested in learning how to build a data access layer and/or typed datasets.

Database Design
I need the perfect mysql_fetch_assoc function for my db class file, i wan't to use it on the following situation:

I want to select and list some fields from a table in the mysql database.

I want to list them by fieldname and not by number result.

I mean not $result['number'] , i want $result['fieldname']

Here is a little example:
=================================================================
$result = $db->execute("select id,name,text from categories where catid=$catid;");
echo ($result[1]!="") ? "<tr><td> Name: </td><td>{$result[1]}</td></tr>" : "";
=================================================================
I want to list the result by fieldname. Maybe so:

echo ($result['name']!="") ? ............................

Design / Primary Key
The situation is that I currently have a excel sheet that is used to store schedule information for three trainers in the following format

DATE | Trainer 1 | Trainer 2 | Trainer 3|
12/1/07 | Mackens | In office | On Course|

My manager wants to place this info into a MySQL DB so that we can develop a php web site to manage the schedule and allow the trainers to view schedule info etc when offsite. I've designed most of the tables whose primary keys are all forigen keys of the schedule table but would like some advice on developing the schedule table in relation to its primary key.

I know that I can't use the date alone as a primary key so I was thinking of using both the Trainer_ID and Day(date) as the primary key as this should allow info for all trainers to be entered for the same date and be flexible enough to allow for growth in the number of trainers without having to edit any external php code etc.

However I was just wondering if I should continue with this method or could someone suggest a better method as I've missed something that an experienced DB admin/developer would spot.

Database Design
I have database tables for folders and a database with documents in it.

The folders table is setup like this

folderid
foldername
folderdescription

The document table is setup like this

documentid
documentname
documentdescription
folderid

I have a user table and want to set it so I can say certain users have permission on certain folder but not others.

I was thinking about adding a group table and putting the users in groups, but then certain users may need access to more than one document folder. I am thinking I will need to add some sort of table that handles just the permissions, but I am not sure.

I am just trying to sort out how to make it so I can put groups in and assign access to folders based on the groups.

Database Design
I usage of mySQL will revolve around a common group + user system. There
can be multiple groups and each group will contain some subset of users.

Each group will have a custom set of data whose values vary per user.

So basically a sample structure might look like this:
(some details intentionally left out)

Database

Table_Group_A
# of user columns

Table_Group_A_UserX
Column 1 Data
Column 2 Data
...
Column N Data

One probably incorrect thought on my part is that it would not be
necessary to store the usernames in Table_Group_A of those users who
belong to that group. But, thinking about it more, it seems like a good
idea. My original intent was to simply look for tables named
Table_Group_A_* and extract the username from the table name...

Does anyone have any recommends concerning this kind of design? I would
like to be able to lay things out in mySQL as cleanly as possible.

Database Design
I've read Ben Fortas book MySQL Crash Course which is a usefull introduction. However, I need to create a database that includes product descriptions, shopping card, stock levels, customer details, order records etc. While the Crash Course book gives some very useful information, I didn't really feel it gave me sufficient information for designing a database. Can anyone recommend any books that cover this aspect in more detail.

I do have the MySQL manual download pdf which I haven't read, but I'm assuming that this wont provide the sort of guidance I need. Presumably a book that also covers topics such as database maintenance/backup etc will be very useful too.

Database Design
I currently have 2 databases. The first one is used to store each user's info. This database has 3 tables. One that stores the login and password, another that stores their preferences, and another that stores their information (email, phone number, name, ...). In each table I use the login (char(25)) as the primary key.

The other database is used to store the posts and replies. I use 2 tables. One stores all of the information for each post and the other stores all of the information for the replies. I currently do not have a primary key for these tables.

First of all, I have tried to break up the User's database into tables based on how much I will be searching for that info.(for example, I have placed only the login and password in a table). Is this a good way to speed up my queries? My database does seem to be normalized with or without splitting the database up into different tables.

Table Design
I have a question about table design, here is the background.

The main table is "Customer" with standard stuff, first/middle/last name etc.. The next table is CustomerType. CustomerType has 16 types and a Customer can have more that one type. So below is what I think is correct design and I just wanted to confirm this is correct:

Table: Customer
CustID INT(11) not null primary key auto_increment
First varchar (15)
Middle varchar (15)
Last varchar (15)
ENGINE=INNODB

Table: CustomerType
CustID INT(11) not null
Type1 enum('Y','N') not null default 'N'
Type2 enum('Y','N') not null default 'N'
Type3 enum('Y','N') not null default 'N'
Type4 enum('Y','N') not null default 'N'
FOREIGN KEY(CustID) REFERENCES Customer(CustID)
ENGINE=INNODB

Is there a better way?

Relational DB Design
I've contructed a fairly simple normalized (3NF), relational database. By simple, I mean each table relates to other table(s) in a 1 to many (1,n) relationship. Now, I am trying to extend this database, but I'm not sure how to proceed given specific qualifiers. Allow me to illustrate:

* 1 Pod has 4 Channels > (1,4) relationship.

* 1 Sensor may occupy 0, 1 or 2 Channels > (0|1|2) relationship.

* 1 Pod has between 0 and 4 Sensors > Indirect relationship between Pod and Sensors. Direct relationship is between Channels and Sensors.

* 1 Sensor connects to 0 or more Pods, but only 1 Pod at any given time. Indirect Many-to-many relationship between Pod and Sensor. Again, the direct relationship is between Channels and Sensors.

I've drawn up a db diagram and attached. PodSernsorAssociation allows a many to many relationship between Pod and Sensor, although it may not be correct to even have this relationship.

Table Design
I need have a table that will have a column that will have a numeric value. In some instances the numeric value will be a percent and in some it'll be a dollar value.

My questions are:

1. What is the best datatype to use? Currently I have it setup as a Decimal(10,2) datatype.

2. Guessing the datatype above is suitable, how can I tell if the value is a percent or a dollar amount?

3. There are some instances where, if the value is a percent, that it needs to be identified as < xx%. Using the Decimal dt, how could I record the "<" that needs to accompany it?

Database Design
What I've got is a database with different
category's and products below them. The thing I'm having trouble with is
the products all have different sizes and colors, and each (different)
product has it's own id and quantity. My first idea was to create one
huge table and deal with it. Is there a better way to do this? Is there
some resources on database design which would point me in the right
direction?

Database Design
I want to design a database that will have 4 main elements:

1. A person database - of individuals and their qualifications

2. An airplane database - of individual airplanes and their attributes

3. A flight database - where 'a person above' flies 'an airplane' with details of the flight

4. A maintenance database - containing descriptions of maintenance items done by 'a person' to 'a plane'

The outputs from this database would be several different log book types, pilots log, engine log, airframe log, propeller log, etc.

I just need a little guidance. I can see where I could just have one single large maintenance table where entries are ID'd with a plane ID and a person ID. But is that the right approach? Should the maintenance log be specific to a certain plane?

The same holds true for the flights. Just one big flights log with entries ID'd bt pilot & plane or each pilot have his own separate flight log???

Design Issue
I've tried a couple of times to design a database for some web programs I'm working on. I keep running into this issue: What if I have a table, and I want one of the data types to be a list. It seems like a table within a table is what I want. The other way I thought would be to create a seperate global table and have a variable that links to the ID of the able I want to contain the list (if that makes any sense). Here's an example:

Character
ID
Experience
Level
Strength

ItemInventory
ID
CharacterID
ItemID

But for some reason that seems really messy to me. Is that the way it's supposed to be done?

DB Design Users
If you have a database with several table and u want users to access the information. What is the available methods to allow these users to access information ( READING and ADDING at least).

Do I add users like the root. Or creating table which hold users informations in which case I wonder how these users access data without mysql user???

Database Design
I have make an lyric databse, the structure is like that

table: artist
artist_id (int)
name (varchar)
gender (varchar)
region (varchar)

table: lyric
lyric_id (int)
artist_id (index)
song_title (varchar)
lyric (text)

these 2 tables only for lyric for single artist. As you music lovers know, sometime artists sing some songs with other artists. Do you know how should I design another table to store those lyrics?

Database Design Tip?
Im designing a database and find that I seem to have a few small tables with say, 3 or 4 fields, where all of those fields are required to identify a record. Is it better to make a composite primary key consisting of all the fields, or introduce a non-composite primary key, like an auto_increment integer?

How To Design The Table?
i have a situation where a:

Table : member
One of the table field : hobby

And my member table need transaction and at the same time, i need a FULLTEXT search to be perform in hobby table

I know FULLTEXT support by MYISAM and Transaction supported by InnoDB

So how can i do that?

Database Design
I have my design like this. I have one table for the protuct that has a
collum for product type. Product type is another table that has links to
other tables like motherboards, hard drives etc. I hope you understand this.
Is that the easyest, most efficent way to do it?

Design Advice
I am looking at designing a mini database to handle subsets of data at my company. You probably know the drill--it's too much to handle with a spreadsheet, but not enough to warrant hassling the DBAs with loading it into our main servers.

So I am looking at managing the data via a local instance of MySQL. The largest file I need to manage at the moment is about 100 columns x 165k rows.

Obviously, I wouldn't build table of that size. That's the whole point of relational DBs.

But here's the deal, at my company, once someone finds a way to do something, it tends to stick. So I'm looking at solving a few temporary problems by managing subsets of data from our main DB.

I have this suspicion that once I am able to do this, the demands will grow, and all the sudden, I'll get stuck managing a whole bunch a data for a bunch of people.

So I want to build the DB right. I've never built a DB before. I just query the main DB.

Without getting into proprietary details, I can say that I am looking at unique IDs that correspond to clients--about 165k, plus all relevant associated data.

What I am tracking is which clients have performed what jobs for us, when, and what the result was.

I need to be able to run reports on this, and I need to add data at the end of each week.

Given all of the above, what general advice do you have? Are there any huge pitfalls to avoid? Any thing that I absolutely should do? Keep in mind that for now, this is just going to be run on a Core2Duo Desktop with 2 gigs of ram, so anything I can do design-wise to speed things along will be useful.

Luckily, I am not in a time crunch. But thanks in advance for whatever advice you are able to come up with.

Database Design Qn
I have a user_account table consisting of an auto id, email address and
password fields.

The id is made the primary key. Is that a good idea or should i just
discard the id and use email address instead?

If I were to have the email address as a foreign key in another table,
will the space consumed bigger that if i were to just use the id?

Which criteria should I give more priority to in deciding which way to go?

Design Question
I'm a somewhat new user of MySQL and I'm looking to use it in a business I am starting. I'm getting ready to start the planning phase for the databases that I'll need but I have a question that I have been unsuccessful in finding an answer to and I hope you can help.

My business will involve selling data files that will be accessible online. I will have a storefront online that will allow users to shop for the data on their own and once they check out, will be able to download the files. Since I'm fairly new to this, I don't know how to bring it all together. Should a database of this nature just store links to the physical location in the filesystem where the data can be found or do people usually store the actual files within the database itself? I hope I've explained this adequately but if I haven't please let me know and I'll try to clarify it better.

New Database Design
I am creating a database of used books for my website and am new to Mysql. My question is this:

should I use the normal form design, or use one table?

I will probably not exceed more than 5,000 books (not in the near future at least. By the time I exceeded 5,000, I would probably have a new site).
I will be updating it almost every day.
I want to query the database using php, with maybe 4 or 5 optional criteria (i.e. author, title, edition, publisher, etc..). With the option of having all those criteria being used in one query.
I would like to also store images for maybe 500-1000 books.

Knowing these things, and the fact that I am a beginner (and normal form querying and altering does seem quite difficult!) can someone advise me as to how I should design my database?

Database Design
I am implementing a web site which will have to support multiple pages of articles (you've all seen "page 1 of 10" on many sites). Since each page can be fairly large and if someone requests page 5, for example, I don't want to load the whole article. I'm visualizing article as a linked list where each page is a node and 'next' pointer tells me which row in 'pages' table to select when a user clicks on "next" page.

Database Design
I need a database which tracks individual users progress in accomplishing 9,100 identical tasks. How can I structure table(s) to accomodate thousands or millions of users. One table with a record for each user would have 9,100 fields which I believe is beyond the limit of some if not all MySql table types. Not to mention it would make one extremely large table. On the other hand, if I go with individual tables for each user that would mean perhaps a million tables. It seems that having thousands of tables open at one time would adversely effect performance.

Database Design
I am designing a database for a dating website that needs to be able to hold hundreds of thousands of records and still be searchable.

What is the best way to go about this.

Should i just define indexes for all of the fields i need to search by or are seperate databases the way to go?

Database Design
is there any database design tool for mysql in which we just design database and it generates database scripts.

Design Guidelines
Are there any (official) design guidelines regarding tables, column names (like primary keys and foreign keys)?

Database Design
A complex network configuration will be modeled into a database: nodes, ports, paths, links,We suppose that the model exists and the database is created
Now the issue is: each user should be possible to create it's own network configuration, store it into database, and work with it.
So a user can have 10 networks configured. The same for each user.
Usually a user works with his own configurations. He doesn't need the configurations of other users.However it should be a restriction that a user can not see other user configurations.
The question is: which is the best way to implement this ?
Ex:
1. each time a user is added, duplicate the database for that user.
2. keep everything in a database but add a column user in most of the tables.

Database Design
I'm try to design a database where students can take class, and teachers teach classes. The tables are: users, enroll ( to connect users table and class table), class, course, and roles (to determine the privilege of the user whether is a student or teacher).

Because of the security reason, I would like to seperate the Roles from within the Users table, but then I run into trouble when designing the "roles" table.

Users table: User_ID (PK), User_Name, etc...
Roles table: User_ID (PK), Roles_Teacher, Role_Student

1st question: How can I link these two tables together?
2nd question: Is it better to seperate students into Students table and teachers into Teachers table, or just put all of them into Users table to simplify the design?

Table Design
This is more related to table design, but there is no forum for that so I figured I post it here since I'm using a MySQL server. Basically I have two ways of designing the structure a table and I need advice. The app I am building requires that a user have access to certain files, so I have to store which files they have access to in a table. I can either create it with two fields: one for user id and one for the files they have access to. The other way I was thinking of is to create a table with one field for user id and one field per file. The second option would allow me to create a client adminstration screen easier since there would only be one row returned per user. But it does run risk of having a lot of fields.

Database Design
I would like to create a ticketing system using MySQL. Since I have never done any database design/data modeling I am looking for someone to point me in the right direction in terms of reference materials, examples or anything else you feel will help me. I will attempt to outline what I am trying to accomplish below.

data to be stored:
1. customer data
2. employee data
3. vendor data
4. contact data
5. parts data
6. scheduling data
7. work order data
8. parts order data

The thing I can't seem to get my head around is how to create and store lists (e.g.. customer contacts, vendor contacts, employee contacts, work order contacts, etc... Also how to create and store a list of parts for a parts order.) while maintaining database normalization.

Table Design
What is the preference with tables and column numbers?
I was planning to build a table with 45 columns but is it better to split the table in to smaller tables with 10 columns each and then connect them via a primary key?

Database Design
I've been looking over all the database design threads and they all seem to say 1 database one table for everything. It seems like that would decrease the search time for my program if it has to look through everything.
What I was thinking was 1 db and 2 tables. One for products the other for reviews. I'm anticipating about 10,000 products and any number of reviews. Combining these two would over bloat my products table - I don't see anything wrong with seperating the tables. Perhaps I'm wrong. I'm a total SQL newb but I'm trying to get everything right as possible.

Database Design
working on building a database to hold all the information for "new world of darkness" characters.I'm building it with mysql, FYI.I'm not sure on some things.
one is how to handle merits each character can have an unlimited number of merits, each merit is ranked 1 to 5 dots. and some merits can have additional... 'strings'.
the main table will probably be just the character table including names, supernatural 'race', faction, etc.I'm going to give a table for merits including merit name, merit_id , book it came from, page number in book.
should I have a table like this?

character_merits
char_id
merit_id
dots
extra_info

or is there a better way to do this?
also if I separate merits from the main table, should I include attributes? or should I put attributes in there own table? each attribute can only have dot's.

Database Design
I am trying to create a MySQL database. I have my info in Paradox databases. The databases are builders production records. Some are separate databases per year (neoplan1981, neoplan1982, etc). Which would be the best way to do this in MySQL? One database for all of the builders (neoplan, mci, etc), with each in a separate table, then the years in a separate table, or create the tables as I already have them in Paradox?
My goal is to have an input field on the web page. Then having the returned data be a combination of the buildres databases. For example, I would like to see all records for octa (octa having 1 record in neoplan1981 and 2 records in mci1984), so the resuls would show all 3.

Table Design
I have a bunch of baskets. There are about 6 types of baskets, and some can only contain fruit, another vegetables, another cookies, and so on. Furthermore, each is designed to be able to only hold one of each type (i.e. the fruit basket has a slot for an apple, an orange, a peach, and a pear). Some baskets have more slots than others. Also, it is possible for a basket not to be full and have empty slots. Lastly, each basket type has a couple of unique fields (i.e. fruit basket has location grown, cookie basket has chefs name, etc).

I need to create a list of all items in all my baskets along with the basket number and basket type such as:
Basket 1, Fruit Basket, Apple
Basket 1, Fruit Basket, Peach
Basket 2, Vegetable Basket, Radish
Basket 3, Fruit Basket, Peach
...

I am having a difficult time figuring out how to structure the tables.

Table Design
I need a DB design to emulate a operating system so I can add folders
(Categories) with unlimited folders (Sub categories) in side folders

Design Tables
I have the folowing situation.I need to store pair shoes.

I have 5 models:
KC-140
KC-141
KC-142
KC-143
KC-144

each model has diffent sizes and colors:
ej.
KC-140 exist in black, red, and brown, sizes from 5 to 12

each kind of piece need to have invetory control and different price.I'm programming with PHP, at the end-front page, the user will be able to select the color and the size. with a drop down menu. Of course, the table structure has to let me manage different quantities for each different style.

Database Design
Hope you can help a little I am hoping to develop a website which will be for a charity looking at bringing carers and people who need help together. I have developed the site and I am now looking at developing the MySql database.I am slightly unsure how to design it.On the one side there will be people who need help, this table will contain there id, first name, last name, address, email, disabilities. And on the other side will be people or organisations that can help this table will contain there id, name, address, email, disabilities that they have experience in.I can establish that the disability is common between them, but I am unsure of the whole Normalization process i.e First Normal Form, Second Normal Form and Third Normal Form.

Database Design
I have a simple site where many items are listed and each item can be rated 1 through 10. The obvious database design would be to have one table with items (ItemID, Name, ...) and another for ratings (ItemID, UserID, Rating), but will I run into problems later down the road with this design?
Also, since I want to easily be able to retrieve the overall rating of any item, should I store that information separately in the items table? Or is it not computational expensive to calculate the average over a table whenever I need?