Thanks Mark, that's really helpful and you confirmed my thoughts - more tables, less code! I will be doing something like this (an example for the show entity):
ShowList -------------- ShowListID (PK) UserID (FK) Title, Description ShowListItem ------------------- ShowListItemID (PK) ShowListID (FK) ShowID (FK) Ordinal Comments, i.e. 'Just cos I love it so' I feel confident that this approach is the way forward now, thank you. On 11/01/2008, Gaulin, Mark <[EMAIL PROTECTED]> wrote: > > Hi > It looks like you have to decide between two approaches: the > one-table-per-list-type method requires a lot of tables but it gives you > explicit foreign key relationships and straight-forward querying; the > one-table-with-a-type-identifier method requires that you define some > sort of "type" field that will tell you what the "id" (of a list member) > is (and so which table to join to). > > I have used the second method successfully but it requires lots of > dynamic SQL (to JOIN to the right table based on the type of list). > Stored procedures tend to be harder to write in this style because > getting the right table name into the query can get ugly, but querying > from CF is not too bad because you can insert the correct table name > into the SQL using CF before the query is run. > > Both of these models assume that you will know the type of objects in > the list before you query it. I would make sure to model that > relationship at the level of the list itself, rather than just > exclusively at the list-member level. So, if there are [user] and > [user-list-member] tables then it is good to have a [user-list] table > too, where each list is given an explicit id and "type", and all > [user-list-member] records refer to the list id. > > --- > > Here's a question: When querying the user-lists, will you want to > return (via SELECT) fields that are specific to the object type in the > list, or will getting just the ids of the list members be enough? If > you need lots of per-object type fields to be returned too then you > might not get a lot of benefit from a generic list table, but if all you > need are the ids (to use with a separate query) then the generic list > model can work really well. > > --- > > In the end, having lots of tables is not that high a price to pay if > your code (and db model) is easier to understand. Either approach will > work, with each being more of a pain in some circumstances than the > other. I'd say pick one and go forward with it to get a feel for the > kinds of queries you need to do. As you learn more about your actual > querying use-cases, you'll be able to pick one model over the other with > more confidence. If you have to switch models, then switch. > > Good luck. > > Thanks > Mark > > > > -----Original Message----- > From: Dominic Watson [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 10, 2008 11:24 PM > To: CF-Talk > Subject: Re: DB Design question > > > > > I'd probably re-think your main tables first. It looks like these > > should be incorporated into one table because shows, mus numbers, etc > > all describe one thing. > > > I simplified my descripition as the relationships are reasonably > involved and it's an ass to describe dbs in text! The tables must > absolutely be there own entites as they have lots of relationships > between each other, i.e. > > 1 Show has many Musical Numbers > 1 Show has many Creatives > 1 Creative Type has many creatives (there are lots) > 1 Creative has many Shows > 1 Show has many Productions > 1 Production has many actors > 1 Actor has many productions > 1 Show has many characters > 1 Actor in a production has many characters > 1 Character has many Musical Numbers > 1 Musical Number has many characters > > etc. etc. !!! > > To add to the complexity, a user is also an actor - there is no actor > table. > > So back to user lists; I think to keep things normalised I would need a > seperate List table for each entity that I wanted listable. So for > musical > numbers: > > MusicalNumberList > ---------------------------- > MusicalNumberListID (PK) > UserID (FK - user who owns the list) > Title > > MusicalNumberListItem > ---------------------------------- > MusicalNumberListItemID (PK) > MusicalNumberListID (FK) > MusicalNumberID (FK) > Ordinal > > The trouble with that is that makes for lots of tables and there are > plenty already (add theatres, locations, reviews........). The up side > is that the relationships are very clear. You want all the musical > number lists for a user, no problem. There is a way using three tables > only but it is anything but normalised: > > ListType > ------------- > ListTypeID (PK) > TableName > > List > ------ > ListID (PK) > ListTypeID (FK) > UserID (FK) > Title > > ListItem > ----------- > ListItemID (PK) > ListID (FK) > FK_Value (FK to a different table depending on listType) - eugh, either > that or hav a nullable foreign key for each entity, stil eugh Ordinal > > > I hope that better describes the dilemma. Really, I prefer the first > method just because I hate the thought of code having to figure out > which foreign key table to lookup! > > Thoughts? Something I'm missing? > > Dominic > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296414 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
