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 |
|
jandrews404
Starting Member
1 Post |
 Posted - 2007-08-28 : 12:25:03
|
| HiI'm fairly new to the SQL world (recently made the jump from Access) and have a question regarding DB design. In my DB, I have around 10 tables that contain data that will be used for lookup/dropdown lists. All of these tables contain only one column of data and a max of 15 records/rows, all of which are unique values. Is it recommended or considered "best practice" to still use an "identity" column in tables with such a small amount of data? An example of one of these reference tables:Table: SubmarketValues: Northeast, Northwest, Downtown, Midtown, Central Perimeter, SouthColumnsSubmarketID (PK, int, not null)Submarket (nvarchar 50, now null)Is it necessary to have the "submarketID" column for this table? Should I create a relationship with the "building" table which will store one of the values from "submarketID" or just create the relationship through a query? I want to make sure that no data in the "building" table would be deleted if a change (highly unlikely) is made to the "submarket" table. Any help is greatly appreciated.James S. AndrewsCarter |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-08-29 : 04:04:43
|
| It's not necessary, since you already have an attribute that uniquely identifies each row. However, NVARCHAR(50) is not going to provide a very efficient key. If no other more suitable candidate keys exist within the domain model (i.e. a 2-3 character code which uniquely identifies each SubMarket), it is appropriate to introduce a surrogate key as you have done. If a real world relationship exists between Building and SubMarket, then you should enforce this. At the moment, data wouldn't be deleted from the building table if SubMarket were to change. However, it could lead to orphaned records, which would be prevented by enforcing referential integrity with a foreign key constraint.Mark |
 |
|
|
|
|
|
|
|
DB Design Question Re: Lookup/Reference Tables