on 8/11/02 2:47 PM, Pekka Saarinen, typed: > 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,
I believe that what you called "intermediate table" books are calling "table map", or something with the word "map" in it. What you have done here is extremely helpful. You don't know how much I appreciate it, especially when I was feeling so ignored -almost like I was "banned" or something! Thank you, thank you, and thank you again. > 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) Could you please elaborate a bit on, "...use same int as you autoincrement keys..." -for example which keys should (or must be?) be autoincremented? > 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. I needed to be told that. because books seem to want you to shy away from over-normalization (beyond level 3). > 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/ That was terrific. Thanks, Lloyd --------------------------------------------------------------------- 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
