July 2, 2008 at 6:59 pm
I'm designing a computer_config table. For historical reasons, I want to be able to store multiple active and inactive dates for a given computer configuration.
Here are the current attributes for the computer_config table:
computer_config_id (int)
hardware_config_id (int)
software_config_id (int)
accessory_config_id (int)
standard_configuration (bool)
sell_price (money)
I was thinking about creating a computer_config_state table with the following table attributes:
computer_config_id (int)
"computer_config_state"
date (datetime)
How should I implement the computer_config_state attribute? Here are two options:
1. computer_config_state as a varchar with values of "active" and "inactive" using constraint enforcement to force specific values.
2. computer_config_state as an INT with a foreign key to a "computer_config_state" table, which has table attributes <computer_config_state_id (INT), computer_config_state_description (varchar)
Thanks in advance for your help,
Jon
July 2, 2008 at 8:28 pm
Or just make it. [font="courier"]is_active (bit)[/font].
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 2, 2008 at 9:46 pm
Using an is_active bit won't work because i need to keep a history of active/inactive dates. If multiple active (or inactive) dates appear, I won't be able to store that relationship.
July 2, 2008 at 10:01 pm
Actually, I was mistaken. Using an is_active bit will work. However, if I ever need to add another type of "computer_config_state", I'll be back to the same problem.
July 3, 2008 at 7:47 am
I've done this two different ways.
First was to add an effective date column and then always sort by effective date to get the latest effective value.
Better was to create a version table and then maintain versions of the data and always get the top version, ordered by the id.
Either way, you only ever insert data. This gives you a real history over time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 4, 2008 at 11:32 am
Grant,
That sounds like good advice.
How would you implement the corresponding computer_config_state? Is either one of the following options better than the other? Or is there a better option?
1. computer_config_state as a varchar with values of "active" and "inactive" using constraint enforcement to force specific values.
2. computer_config_state as an INT with a foreign key to a "computer_config_state" table, which has table attributes <computer_config_state_id (INT), computer_config_state_description (varchar)
Thanks again,
Jon
July 7, 2008 at 6:26 am
Personally, I'd go with option 2. That way, you don't have to worry about spelling and, when another state, "suspended" or something, comes along, you're ready and won't have to change code.
However, as with all answers around here, it depends on the circumstances.
You also need to be cautious about very simple values like this. They tend not to index well, so you'll want to be careful how you build the indexes on the table and how you write the queries against it. Usually, building a compound index with the simple field is the way to go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply