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 |
|
spud
Starting Member
7 Posts |
 Posted - 2004-12-02 : 16:19:27
|
| Hi,I am a relative newcomer to SQL Server and db design. I have read quite a bit, but have yet to find good guidance on the following situation:Background:Say I have a database that's tracking elections. Elections have a season represented by a given year (e.g. 2004, 2005, etc). To properly normalize, my understanding is I should have a table YEARS that ELECTIONS references in a relation using YearID. Similarly, if I have a table for POLLINGLOCATIONS, then I might also have a referenced table that stores the type of location named POLLINGLOCATIONTYPES (e.g. Home, School, etc). Seems to me if you are required to store a lot of attribute information, you could end up with lots and lots of tables storing small lists of attributes and a lot of code related to getting at this information. For common attributes where the lists are fairly static and/or short in length (years, attribute lists of < 5 items), I am wondering if this information isn't better handled in the business logic (i.e. Years list = [...] or LocationTypes = [...]). If your business logic code is well documented, then it seems no more effort to update it for additions or modifications to the list then it does with dealing with additional table and code required in business logic to deal with changes to tables (e.g. don't show 1999 in drop down list any more).Questions:What's the best practice in db/app design (maintenence and performance being priorities) for dealing with common attribute information? What decision criteria are used in helping to make these choices?I run into this a lot when modeling and have yet to understand how to make good decisions. Thanks for any insights and experience you share.J |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-02 : 17:21:48
|
quote:
Say I have a database that's tracking elections.
Well, just to be certain, ARE you tracking elections? The more specific you can be about your situation the better the advice we can give.quote:
To properly normalize, my understanding is I should have a table YEARS that ELECTIONS references in a relation using YearID.
That's one way of doing it, but IMHO if all you need is the year the election took place, just store it in the Elections table. Don't bother linking it to another table for Years, it's unnecessary. You'll never have a single election that occurs over multiple years.I'd extend the same logic to LocationTypes; you don't NEED a foreign key, although in this instance it makes more sense to have one than it would for election year. As for the other attributes, more detail is needed.I'd recommend having data integrity and validation logic in the database, as much as possible, even if you decide to duplicate it in a different layer. You can never have enough data validation, and it's very easy to miss validation if you rewrite or replace a business layer only (or someone else changes your code later on |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-12-03 : 10:55:53
|
| No but you could have multiple elections in the same year (on different dates)....town council, local, state, national, european, referendum....in which case there may be an arguement for years to be a seperate table. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-03 : 11:21:19
|
| I thought of that, but the election itself is already unique. A town election held on two different dates, same year or not, are two different elections. The only time I can see an exception would be something like what's happening in Ukraine now, but even that would be a separate election. I think that if you had to constrain an election year value you can do it with a check constraint and not really need a foreign key.Just my dos centavos. |
|
|
|
spud
Starting Member
7 Posts |
Posted - 2004-12-08 : 11:01:15
|
| Thanks for the replies. You have helped me pick a direction. No, I am not tracking elections (I'll stick with just voting). I hadn't designed enough of my actual model to post so I came up with a quick example to help guide me.Thanks.J |
|
|
|
|
|
|
|
|
attribute info - handle in db design or ?