MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Pekka Saarinen Date:August 11 2002 6:47pm
Subject:Re: MySQL database design
View as plain text  
At 8/11/2002, you wrote:
>MySQL queries.. tables... design.
>
>http://garnet.acns.fsu.edu/~tlr7425/my_tables.gif
>
>There you will see a rough draft of what I am trying to do.
>
>Perhaps you will see some places that I will need to use a table_map?
>
>Or you can advise me of how to arrange my keys, or otherwise develop this
>db?
>
>I'm learning and need this help to better understand keys and normalization,
>while developing a db that can withstand expansion.
>
>I understand that it looks like I am trying to normalize what appears to be
>almost "all the way", I could be wrong about that; but this is what I am
>interested in.

Hi Lloyd,

Spending plenty of time designing the database is be the best decision you 
can make. If the base is solid and flexible you'll end up with a solid and 
flexible application and not rewriting database structure will every small 
feature request.

>Advice from some gurus on something that *I am working on* would help me to
>understand much better than all the books I have!
>
>(I was on this list before, but I had to change my subscription address.)
>
>Thanks in Advance,
>Lloyd

I'm no guru, but I'd like to tell you my view on this as about a year ago I 
struggled same battle and I think I learned a lot from it. Real gurus out 
there please feel free to correct my writings :)

The design should always be related to what the relations there are between 
items you describe with the database. Here I see you are building a 
database of personnel and gear they use, groups they belong to and dirs 
they can access etc. But not much relativity there now. When normalization 
is on level 5 you see mostly id's :)

Ask questions. Think examples of queries you will have to do. Ask yourself 
"what if someone asks me to pull out data that has.... ". Build and test 
queries to get that data. Make assumption that there is a LOT of data. 
Think space usage.

The questions I would ask about your db structure are e.g.:

Will data be repeated (in a table)?

-  there is now a lot of repeated data. A computer table could hold just 
model id and serial, and then have id's for cpu, hdd, ram, ports, 
manufacturer etc.  An IP table could hold IP's, and there is not much point 
storing an IP with a computer as one computer will definitely have more 
than one IP's.
Same way: OSes table could be

OSes
----------------
osID
brandID
displaynameID
date_added
date_modified

or with even more normalization....

OSes
----------------
osID
osdataID

.....and with that you'd have an extra table.....

OSes
----------------
osdataID
os_name
os_manufacturer
os_release_data
os_price
os_etc

....plus to that you'd have a separate table....

OSes_to_comps
----------------
OSes_to_comps_id
compID
osID
date_added
date_modified

.....which will let you have one computer have more than one OS (perfectly 
possible). 'OSes_to_comps_id' is really not needed but I like to have it 
there anyway.

More questions:

How do you link a person to a device?
Or several persons to same device?
Can one person have more than one device?
What if one person has two computers and 4 displays?
How do you link a device to a group (list all devices of a group)
How do you link gear together?

Answer to all above: use intermediate tables, like the 'OSes' example above.
I would do a table 'person_to_computer' which would have 'PersID' and 
'compID'. This way one person can have several computers. Using same 
method: 'person_to_display' table would just link a person to a display. 
These intermediate tables take very little room (just use two int cols or 
so - use same int lenght as you autoincrement keys) and make searching e.g. 
"what displays a person has?" much easier. They also make it possible to 
have complex relationships with minimal effort.

         SELECT
                 displays.make,
                 displays.model,
                 personnel.firstName,
                 personnel.lastName
         FROM
                 displays,
                 personnel,
                 personnel_to_display
         WHERE
                 personnel_to_display.dispID = displays.dispID
                 AND personnel_to_display.persID = personnel.persID

Display detail could be splitted to several tables just like in OSes 
example (you could build  manufacturer and model tables which are shared 
accross the database), so that you could get display info by

         SELECT
                 manufacturer.manufacturer_id
                 manufacturer.manufacturer_name,
                 model.model_id
                 model.model_name
         FROM
                 manufacturer,
                 model,
                 displays
         WHERE
                 displays.manufacturer_id = manufacturer.manufacturer_id
                 AND displays.model_id = model.model_id


Heavy normalization will most likely make programming the application more 
complex, but it will pay off in speed and flexibility to change and add things.

PS. About table names:

- mixing case will make you make coding/typing mistakes.

- if you intend to program with PHP, better use column names 
like  "displays_id" and "displays_size" instead of just "id" and "size" 
otherwise you'll end up using aliases all the time.

- prefix you table names with project name, like "GRT_computers", 
"GRT_personnel", this way you can place the database into non-empty 
database knowing there almost no chance of overlapping names.

Hope this helps,

Pekka
http://photography-on-the.net/


Thread
MySQL database design, one column, 10 entries?david11 Aug
  • Re: MySQL database design, one column, 10 entries?Bhavin Vyas11 Aug
  • Re: MySQL database design, one column, 10 entries?Quinten Steenhuis11 Aug
    • MySQL database designtrogers11 Aug
      • Re: MySQL database designPekka Saarinen11 Aug
        • Re: MySQL database designtrogers12 Aug
          • Re: MySQL database designPekka Saarinen12 Aug