----- Original Message ----- From: "Joseph, Smile Poet" > Classification tables: > a number with the simple structure: > LIMERICK Lim_id, poem_id > HAIKU haiku_id, poem_id
Let's have a quick run through relational databases shall we? You have a bunch of poem classifications. Each can match many poems. You have a bunch of poems. Each may fir into more than one class. Therefore you need three tables: one to list the classes, one to list the poems, and one to list the links between the two: POEMS CLASS-INDEX CLASSES poem-id ---> poem-id poem-name class-id <--- class-id poem-date class-name poem-author .... Thus there is one entry in class-index for every "fit" between a poem and the classes of poems. Given the following examples: poem classes: 1 - Limerick 2 - Haiku 3 - Doggerel 4 - Comic Verse ...the following poem could fit #2, 'Haiku', as well as #4, 'Comic Verse':: "Three things are certain: Death, taxes, and lost data. Guess which has occurred." So, in class-index you make two records, one with the id of the above poem and class-id = 2, and another where class-id = 4. Then you write nice complicated SQL queries for Rudy to improve on, like: SELECT poem-name, poem-author FROM poems, class-index WHERE poems.poem-id = class-index.poem-id AND class-id = 4 In other words you can find all the poems whose id appears in the index table against the id of a given class. This is definitely more "correct" than having a field in the poems table for class that can say "limerick" or "haiku, comic verse" and doing a search for poems where that field contains "haiku", even though that works just as well really :-) The best way to design your database is, erm, a bit like Access does it <looks guilty> You draw little boxes on a sheet of paper, one box for each table, with the table name at the top and the names of the columns (fields) underneath inside the box. Then you draw little arrows that join up the boxes to indicate the relationships between them. If my attempted diagram above reads the same in your email client as it does in mine, you should get the idea. Each table should (ideally) have one value that uniquely identifies each individual row (record) and then you look to see if any of the fields can be moved out to another table, as we have done with the classification type. Don't worry about the SQL queries needed to put it all back together for the time being. Maybe it helps to think about how you might enter the data - is it a field that would have to be typed in, or could the value be chosen from a drop-down box? If so, that probably needs its own table. Sometimes it might seem a waste: for instance, there is only one author's name, so why not put the name itself in, instead of an id number to lookup the name in a table of authors? The reason is that things change, first there could be two authors with the same name, second you might get two people collaborate on a poem, third you might decide you want the author's address... in each case life is easier when the author data is in a table where it only appears once, and only the id goes in the poems table. That should keep you going another week :-) ____ � The WDVL Discussion List from WDVL.COM � ____ To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To set a personal password send an email to [EMAIL PROTECTED] with the words: "set WDVLTALK pw=yourpassword" in the body of the email. To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub ________________ http://www.wdvl.com _______________________ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
