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 |
|
slserra
Starting Member
19 Posts |
 Posted - 2002-12-23 : 12:47:16
|
| I am developing an application that manages announcements, events and documents for a company. I need to capture information about the particular item as well as access privileges. I am struggling with the best way to design the tables.Each of the items have similar fields such as Title, Description, Create Date, and CreatedBy in addition to fields that are unique to each item. I see three approaches to designing the tables. Approach one is to make a separate table for each item. For example:tbl_Announcement (title, desc, dtcreate, start date, urgent)tbl_Event (title, desc, dtcreate, dtstart, dtend, rsvp)tbl_Document (title, desc, dtcreate, attachment, checkedout)with a corresponding access table for each item:tbl_AccessAnnouncement (AnnId, MemberId)tbl_AccessEvent (EvtId, MemberId)tbl_AccessDocument (DocId, MemberId)The second approach generalizes the tables into a single table that contains the common fields and secondary tables that contain the unique fields:tbl_Item (title, desc, dtcreate, ItemType)tbl_AnnouncementUnique (dtstart, urgent)tbl_EventUnique (dtstart, dtend, rsvp)tbl_DocumentUnique (attachment, checkedout)with a single access table:tbl_AccessItem (ItemType, ItemId, MemberId)The third approach is to create one single table that contains all of the common and unique fields with an ItemType indicating which fields are applicable for the particular item:tbl_Item ((title, desc, dtcreate, ItemType, start date, urgent, dtend, rsvp, attachment, checkedout)with a single access table:tbl_AccessItem (ItemType, ItemId, MemberId)From a maintainability standpoint I think option three would be the best since new items (such as Links or Contacts) could be added simply by adding a new item type. Minimal table changes would be required. However, this would waste space since each row would contain fields that are not applicable to the particular item type.From a performance perspective let's assume the application contains 1000 Announcements, 1000 Documents and 1000 events with 100 people able to access each item. (these number can grow by 100-1000 times)Option 1 would have three 'item' tables of 1000 rows each and three 'access' tables of 100,000 (1000 X 100) rows each.Option 2 would have 1 'item' table of 3000 rows, 3 'item' tables of 1000 rows and 1 'access' table of 300,000 rows.Option 3 would have 1 'item' table of 3000 rows and 1 'access' table of 300,000 rows. The application needs to support queries such as the following:Show all documents created by a personShow all documents a person has access toShow all documents Show all documents created in JanShow all documents, events and announcements created in Jan.Show all doucments, events and announcements created by a person...In option 1, either a separate query would be needed for each item type or one large query that unioned each item type would be needed. The individual union selects would be run based on passed in parameters.In option 2, a single select could retrieve the common fields and then joined with the particular 'unique' items table.In option 3, a single select could retrieve all fields for all item types.OK. That is my predicament in a nutshell. I would appreciate any comments on how best to design the app to maximize its performance. Is it best to have smaller tables with lots of joins and unions or a small number of large tables with few joins and unions?Thanks in advance for your comments.Steve |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2002-12-24 : 09:31:41
|
| My opinion is a version of option 2. I'd go with a "Types" table like what you have, but you don't necessarily need to have 3 seperate tables for each type if you do this. All of the details that those records share in common would then become 1 table like start and end date etc.Mike"oh, that monkey is going to pay" |
 |
|
|
p2bl
Yak Posting Veteran
54 Posts |
Posted - 2002-12-26 : 06:46:42
|
| I have faced the same question from time to time,but I usually choose option 3,because some ms guy who is a member of SQL Server designer tells me to avoid "join"(Should I think him as the god?).for u instance,I preferred option 3,because u can create some "Indexed Views"(only supported on 2K) for certain query,if u choos option 2 or option 3,u can not index this views which contain "union" or "join"========================look! |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-12-26 : 23:20:11
|
| According to your current numbers, I'd go with #3. Even if you multiply them by 100, they still shouldn't be too problematic- 300,000 rows and even 3 million rows SQL should still be able to handle. If you index the tables properly, reports shouldn't be a concern. And it looks like the additional fields unique to each type are mostly bit fields(urgent,rsvp,checkedout) and date fields which don't take up much space.The best idea: Try out #3 with lots of test data. If you don't like the results, maybe try #1 which is the most difficult to work with maybe, but the most normalized and flexible to changes.Sarah Berger MCSD |
|
|
|
slserra
Starting Member
19 Posts |
Posted - 2002-12-27 : 03:46:37
|
quote:
According to your current numbers, I'd go with #3. Even if you multiply them by 100, they still shouldn't be too problematic- 300,000 rows and even 3 million rows SQL should still be able to handle. If you index the tables properly, reports shouldn't be a concern. And it looks like the additional fields unique to each type are mostly bit fields(urgent,rsvp,checkedout) and date fields which don't take up much space.The best idea: Try out #3 with lots of test data. If you don't like the results, maybe try #1 which is the most difficult to work with maybe, but the most normalized and flexible to changes.Sarah Berger MCSD
One more question before I begin. Lets say I want to find all items that contain a certain text string in the title or have a start date before a certain date. Would option 3 be more efficient with this type of query compared to option 1. Basically, is it more efficient to perform a single query against a large table or multiple queries unioned together against smaller tables? Thanks for your input.Edited by - slserra on 12/27/2002 03:50:04 |
|
|
|
|
|
|
|
|
Design of similar tables