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 |
|
geossl
Yak Posting Veteran
85 Posts |
 Posted - 2008-09-06 : 00:03:51
|
| Dear All, There is a scenario that any member of a retail company can apply for VIP status for only one product type at a time. The VIP application needs approval. After the approval, the VIP application is valid. However, VIP member can apply for another product type VIP membership. When the new application is approved, the old VIP member is void and cancelled. There are two designs:Option 1:Member Table:- MemberId- Name- ….VIPApplication Table:- ApplicationId- MemberId- Date- ProductTypeIdVIPApproval Table:- AprovalId (Primary Key)- ApplicationId (foreign key)- Date- Approve (boolean)VIPMember Table- VIPId (Primary Key)- VIPApprovalId (foreign key)- Date……Option 2Member Table:- MemberId- Name- ….VIPApplication Table:- ApplicationId- MemberId- Date- ProductTypeIdVIPApproval Table:- AprovalId (Primary Key)- ApplicationId (foreign key)- Date- Approve (boolean)VIPMember Table associated to Member table- VIPId (Primary Key)- MemberId (foreign key)- Date……Which design seems better? Easier SQL for report? Better operation in program? |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-09-14 : 17:21:27
|
| Do you need to cater for multiple approvals from the same member for the same Product Type? If not then you manage with just 3 tables & move the date(_approved) to VIPApplication and change the name of the other date to application_date. A suggestion in terms of naming primary keys- use exactly the same name as the table & append Id, this way isn't very clear when the column is seen in SQL which table it belongs to. |
 |
|
|
|
|
|
DB Table design tradeoff