Bj, Got it! I am sure you have given a similar explanation to someone else not so long ago. The thing that hadn't quite connected is entering an item to be classified more than once into a table. That will do exactly what I want, which is to be able to list and/or count all poems of a particular type.
And I realise Rudy is pointing me to that on the publishing tables. Hey ho, back to the drawing board just as a threatening cold is making me take various dulling liquids (any virtual scotch going, michael?). There are lots of refinements in this, aren't there? Thanks, Joseph > ----- 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.unsub%% > > 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. > ____ � 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.
