December 21, 2008 at 7:15 am
Suppose we have a business database containing (among others) an "Orders" and an "Invoices" table. We want to be able to record notes/activities about any order or invoice --- by date, author, and activity comments (essentially an activity log), so we create an Activities table containing ActivityID (PK),ActivityDate, Author, and Comments.
In order to use the Activities table for both Orders and Invoices, we create create 2 additional tables (OrderActivities --- consisting of columns OrderID and ActivityID and InvoiceActivities --- consisting of columns InvoiceID and ActivityID)
However --- the relationship would be one->many from Orders->OrderActivities but only one->one from Activities->OrderActivities.
This is just an example -- in reality, I have appr 10 tables that will need "child" activity tables.
Can anyone suggest a more efficient database structure?
Thanks!
December 21, 2008 at 7:42 am
barbararyan (12/21/2008)
In order to use the Activities table for both Orders and Invoices, ...
This is the flaw in your design: there is no reason in relational design that you should have only one Activities table for multiple associated parent tables.
Instead you should have an OrderActivities table, and an InvoiceActivities table, etc. But according to what you have told us so far, there should be NO common "Activities" table because there is no common data that would require relating all of those "X_Activities" tables together.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 21, 2008 at 8:13 am
I considered creating multiple activity tables, but a database with 10+ virtually identical tables (except for a FK to the parent table) just doesn't seem to be normalized.
December 21, 2008 at 8:18 am
Also, there MAY be a need to examine ALL activities (i.e, including all orders, invoices, etc.). We may add a few more columns to the Activities table(s) --- "ActionsRequired" and "CompletedOnDate". We may want to print a list of activities for which there are actions required and whether or not the actions were completed.
December 21, 2008 at 9:28 am
barbararyan (12/21/2008)
I considered creating multiple activity tables, but a database with 10+ virtually identical tables (except for a FK to the parent table) just doesn't seem to be normalized.
Relational design is based on eliminating redundancy of non-key data in your data tables. It does not include trying to eliminate redundancy in your metadata.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 21, 2008 at 9:36 am
barbararyan (12/21/2008)
Also, there MAY be a need to examine ALL activities (i.e, including all orders, invoices, etc.). We may add a few more columns to the Activities table(s) --- "ActionsRequired" and "CompletedOnDate". We may want to print a list of activities for which there are actions required and whether or not the actions were completed.
Relational design should be driven by the data and not by the queries that you want to execute against the data.
Do the relational design first. Then worry about your data management and query issues after that. If you do the first one right, then you will have far less to worry about in the latter one.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 22, 2008 at 4:47 pm
The solution is very simple. You need to create a parent table called Activity. Off this table is
1 A child table called ActivityNote with a one to many realtionship.
2 A child table called ActivityOrder which is one to one.
3 A child table called ActivityInvoice which is one to one.
This design concept is called generalisation/specialisation. The Activity table can store data that is common to both the Order and Invocie such as date, customer, status etc. Genspec database design is very elegant, although the hardest part is retraining your brain away from the appalling Northwind/Adventure Works model.
December 22, 2008 at 5:20 pm
Steven MacLeod (12/22/2008)
This design concept is called generalisation/specialisation.
Any chance we can get a link or reference to this WRT Database Design?
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 22, 2008 at 5:33 pm
Not sure what an WRT design is but if you google you should find, except I'm from New Zealand where ironically our zeds are esses (z's are s's) so try
Generalization Specialization.
It's not a quick solution as it took me several years to get my head around it and many more to make any money out of it.
By the way, this design method becomes really useful when you have many child tables. I have many activity specialisations coming off my Activity parent table.
Steven
December 22, 2008 at 6:02 pm
Thanks....I think this is basically what I ended up with --- A table "Activities" (which contains ActivityID, ActivityDate, Author, Comments, etc). There are 2 child tables --- OrderActivities and InvoiceActivities (with a 1-1 relationship to Activities). The relationship between Orders and OrderActivities is 1-many (as is the between Invoices and InvoiceActivities)
I'm not sure what ActivityNotes is from your example?
December 22, 2008 at 7:30 pm
Steven MacLeod (12/22/2008)
Not sure what an WRT design is..
WRT = "with respect to"
but if you google you should find, except I'm from New Zealand where ironically our zeds are esses (z's are s's) so try
Generalization Specialization.
Already tried that. Everything that I found was on client class design, not on database design, which is a very different thing, of course.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 9:55 am
I’d create a single-column table (eg "ActivityType") which stores the types of Activity – Order, Invoice, etc. Then have this field as a FK in your Activities table. Also in your Activities table you would need another FK (say "DocumentID") to store the OrderID/InvoiceID.
This way you would do away with the need for multiple Activities tables. And by using the ActivityType field you would have the flexibility of querying on all activities or only ones relating to a particular type.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply