Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-10 : 14:53:50
|
Paul writes "I'm the lead developer on a large MRPII system we have about 54 or so "primary tables" and many more secondary tables (detail tables, many to many tables etc.) The whole thing is very relational. We have used GUIDs for all the primary keys and that's worked out very nicely. It means we can perform joins and "merge" unlike data very easily. For example, an allocation source can be a POdetail or an inventory item, but the foreign key just says SourceID and can point to either table. DRI is out and we perform the complex RI in triggers along with other business rules.
We're also storing every field level change in a huge audit table (hey disks are cheap these days). We've done this before and it really works well. It even saved out butts when a law firm hired by a client to enter data claimed they couldn't use the system for ten days and wanted to sue us. But the 40,000 entries int the audit table was better than any Gore recount). The thing that makes this possible is that every table has a GUID as the PK, so it's real easy to just store in the sudit table the table name, PK, fieldname, change description, and user, date stuff. It's all done by triggers and runs great.
Here's the question: To pull together the whole list of audit data we need to join back with the original table and get more row data, like the name etc. A view seems to only support about 24 tables in a union all. More than that and table mysteriously disappear from the view. No Problem we put it in 4 views and the unioned the results from those views. Runs Great.
But I wonder if another approch might have wored better. If a table, "main", contained a row for every row in every other primary table and the common data (PKID, name, dewscription, isdeleted, createdate, modifieddate, isactive), and a view was put together for every table to join it with main table, that would make working with every object type as easy as working with all the object type.
I previously built an OOdb on top of SQL 6.5 and it was OK, but the tools were lacking and reporting was a bear. But in this case would you have joined 54 tables, or used a common main table? pros and cons?
-Paul" |
|
|
|
|
|
|
|
54 table union view vs. OOdb like main table