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:296411
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
