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/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
