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 |
|
ysuwardy
Starting Member
1 Post |
 Posted - 2011-07-22 : 04:13:34
|
| Hi everyone,I'm in a dilemma with transactional table design and hoping you guys could advise a best approach.Two master tables: tRegistrant, tCategorytRegistrant: stores registrations.tCategory: stores various categories a person can register to (includes desc, price etc)One registrant may register to more than one category, hence I would need to join the two tables and create a new table: tRegistrantCategory.Now in tRegistrantCategory, I'm confused between using foreign keys to tRegistrant and tCategory, or make duplicate of columns from the respective table. If foreign key is used, when somebody modify the content in master table (e.g. price in tCategory), the new price will be reflected when records are retrieved from tRegistrantCategory. However that may not be accurate because at the point of registration, the registrant is actually registering to the old price.Could you guys advise me what is the standard approach to design a database for this kind of scenario? Must I make column duplicates in tRegistrantCategory?Thanks in advance! |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2011-07-26 : 07:29:13
|
| You want both. The RegCat table is correct and the FK ensures the category exists and has a description. Then wen you create the relating row, copy the price from the category. Nothing wrong with that at all. |
 |
|
|
3magic
Starting Member
14 Posts |
Posted - 2011-08-03 : 06:15:14
|
| You have to use foreign key to communicate between two tables. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-03 : 07:32:12
|
| once you change price, how will a registrant register against old price? only new price is available in category rite?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Join-table design