SQL Server Performance Forum – Threads Archive

added value to uniting tables?

I am leading a team of programmers who are re-structuring an existing database. We have a difference of opinion on the following question. I would really appreciate anyone’s comments and help on the matter.
We have three tables representing tasks, appointments and messages. The three have similar data fields (although not identical). My programmers say that we should unite them into one table, and use views and flags to differentiate them from each other. Also, this means that some fields will necessarily stay null (or 0) for some records, when these fields do not match the type of record they are in.
Their strongest argument is that these three tables all use similar junction tables to associate themselves to a users table, i.e. tasks, messages and appointments all have recipients (appointments have attendees, which is the same thing). So, they say, why create three table structures which are similar, instead of combining them into one and then compensating for the differences?
My claim is that as long as these three entities are different in definition (at least in the eyes of the user), there is no point in joining them together, and then creating outside mechanisms to turn them back into regular tables. It doesn’t seem to save us anything, It creates confusing super-entities which basically contain everytning, and makes it a pain if we ever want to upgrade functionality in the future just for one of these features.
Again, all comments will be appreciated.

I would support your point. When you theoretically think what a table should model, I think there is no other way. Their argument with the junction tables is irrelevant in this context. Each table models an entity and a message entity is completely different from a task or appointment entity. And actually nothing more that needs to be said. Hey, you should encourage your developers to post this question on the<a target="_blank" href=news://microsoft.public.sqlserver.programming>news://microsoft.public.sqlserver.programming</a> newsgroup. With a bit of luck, they’ll catch the attention of Joe Celko. Might turn out interesting. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank http://www.insidesql.de</a>

Found a link that’s really worth reading. Although on lookup tables, it also touches on the nature of tables in general. Get a cup of coffee and enjoy:http://groups.yahoo.com/group/sql_server7_forum/message/5731 —
Frank
http://www.insidesql.de

regarding Joe Celko comments on who makes this type of mistake, i think it is more of the second than the first

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] That’s a good read. Too bad he’s such an ass on the forums. I’m mean, but I do try to at least give good advice without making someone feel like a complete idiot everytime I do it. There are some people who I get downright nasty with, but that’s because they’ve shown they are impossible idiots with no hope of redemption. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Gee, I just blew my whole point out of the water, didn’t I. Just a few comments now on the original post: –We actually created a "message center" system similar to what you have now. We used a super-entity called the "message center" to model the common attributes of all messages, notes, etc in the system. We then created join tables to anywhere we wanted to join to display the center. Each area though (notes, messages, etc) had it’s own specific table. It allowed us to not have NULLs which are usually (99%) a sign of bad design. At the same time, the message center was easily expandable to provide functionality to multiple areas of the system. The MATRIX tables had the message_center key and a bit field to determine the exact kind(s) of message each message_center record had attached to it. –The only reason we did this was because of the speed of development, and the lack of specification and research on the project. We knew that we needed to quickly provide the entire functionality of our message center to multiple objects. For example, we ended up assigning messages to loans, leads, loan officers, applications, disclosures, etc. Sometimes the same message_center (which could have both a note and a message, or multiple notes and messages, plus maybe an email) could be attached to multiple objects such as both a specific loan, a loan officer, and a loan officer manager. In our instance, it made since to have the super-entity. There are some dangers though: 1. Don’t use this type of system EVERY TIME you want to attach a description or message to something. The message center was it’s own "application". As such, it existed independently as it’s own subject area. We could provide the functionality to any other subject area, application, database, etc that we needed to. There are places where it’s now over-used though, or where enough business knowledge hasn’t been put into the matrix tables to make it quickly queried against. 2. The structure can become EXTREMELY large very quickly. Remember, if you have 400k loans and there’s just 5 messages, a couple notes, and 4-5 emails per loan you now have a very large dataset to support. This is a perfect example where partitioned views can be used. 3. Don’t make the decision lightly. If you’re not providing any value to the business by having "super-entities" then avoid them at all costs. The person who designed the system above also decided that EVERYTHING needed to be a "party". Instead of joining directly to the entity, he carried this stupid party key all over the system. It’s now a fairly large probem with hundreds of thousands of lines of code, which are going to need to be changed. 4. NEVER, EVER, EVER combine the entire section of your entities into one central record collection(call it a table if you want). Your developers are dead wrong on this. You will spend hours and hours correcting this mistake if you don’t take a strong stand on it now. Hope this helps. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

Thanks, everyone. We have found a middle-ground solution which is more object oriented in nature. I will forward the comments and explanations to my team. __coder

Object oriented/SQL Server?? Would you mind explaining your solution? I would be interested in seeing what you ended up going with. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Let me guess: You apply inheritence by linking specialized object specific properties (additional columns actually) using the same pk value as more general entity. General entity (table) contains common attributes (columns). <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Appologies for the late reply… mmarovic – you were right. I don’t know how appropriate it is to use the term "object oriented" about this technique, but basically we created one table with the most abstract entity we could define out of the three (tasks, appointments, messages) and added all other properties as extra tables with foreign keys to the main table. The only problem with this is that we’re accessing each object at different "entry points" in the tree of tables, which means we need to write a little extra code for checking what can be joined with what… However, it does simplify the database design.
Thanks for your help everyone!

]]>