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 |
|
Morke
Starting Member
5 Posts |
 Posted - 2011-10-18 : 06:54:24
|
| Hi,Database: MSSQL 2008 Express R2I wonder what thoughts are on the following scenario. I am currently writing an app for a large organisation with some 80,000 members. A members may be join or be assigned to a number of designated groups. "Vocational Groups" consisting of people who work in specific job classifications. "Branch Sections" consisting of people who work in particular areas. (E.g. Health, v Local Government ). "Committees" people who are on specific committees, and their various roles on these committees. Etc.Currently each of these groupings had their own tables. Eg. "Vocational_Groups"vg_id vg_name...Membersme_idme_name...Members are assigned to a group Vocational_Membersvg_idme_id...The same would take place for other groupings.Currently there are 14 tables: Seven containing the group names and seven containing the actual members. Each having various numbers of records. They are very quick to read into grids, etc. Joins are simple and straight forward.I am thinking of merging each of these tables into one long table. Lets call it "Group_Members". This would result in a table of some 7,500 records (note a member may belong to multiple groups) having the following structure.Group_Membersgr_idme_idAnd a corresponding lookup table. As a consquence there would only be two tables as opposed to the fourteen ( one for each of the lookup group tables, and one for each of the related members). Can anyone see anyone see any benefits in this longer 2 table concept? Benefit: Only two tables? Normalisation: betterDownside: Joins? A member's record might bave to be pulled out multiple times to display his membership in each of the different groupings. This might be to a grid, or to a form, or be required in a report. Speed?Looking forward to hearing any ideas.Morke |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-10-18 : 07:02:10
|
| I would go for the two tables (presumably 3 with a conjoint table?)If you need the extra tables then they can be built from this but with such a small amount of data it shouldn't be a problem.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Morke
Starting Member
5 Posts |
Posted - 2011-10-18 : 10:32:41
|
| Thanks for the reply,That's more or less how I feel. However, is it more efficient to do multiple joins into the same table, rather than opening and reading a specific record from multiple tables?Morke |
|
|
|
|
|
|
|
|
Lookups: Multiple Short or Single Long Table