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 |
|
KabirPatel
Yak Posting Veteran
54 Posts |
 Posted - 2008-03-09 : 15:46:15
|
| Hi,I have a questions regarding database design.I currently have data in a table (TABLE A) as follows:CountryCodePackageID PackageID CountryCode DeliveryMethod Comment-------------------------------------------------------------------------1 Package_o1 US Method1 Comment12 Package_o1 GB Method2 Comment23 Package_o2 US Method1 Comment34 Package_o3 US Method1 Comment4etc....The CountryCodePackageID field is an identity column and the Primary KeyThe combination of PackageID and CountryCode is unique.The Delivery Method and comment is dependent on the combination of PackageID and CountryCode.I also have a table (TABLE B) as follows:DeliveryMethod ----------------Method1 Method2 Method3 etc....Their is a foreign key constraint from TABLEA to TABLEBI consider this to be in 4NF (correct me if I am wrong).I have been told that the DeliveryMethod field needs to be stored in multiple languages. In another words for each of the delivery methods in Table B above I need to store translated equivalents.e.g. Method1 might be called "ZZZ" in another languageI believe that their are 2 ways of storing this data:1) Amend Table B above as follows:DeliveryMethodID DeliveryMethod ---------------------------------1 Method1 2 Method2 3 Method3 etc....and create a new table (TABLE C) as follows:DeliveryMethodID Language DeliveryMethod------------------------------------------------1 EN Method11 GE GermanText1 SP SpanishText1 FR FrenchTextetc....This seems fine except that there seems to be some overlap between TABLEB and TABLEC above. Also TABLEA above would now reference the new Identity column.2) Add all my data to the existing TABLEB as follows:DeliveryMethod Language DeliveryMethodTranslated--------------------------------------------------------------------------Method1 EN Method1Method1 GE GermanText Method1 SP SpanishTextMethod1 FR FrenchTextetc....and leave TABLEA as it is.Could somebody tell me what the best method would be?Many thanks,Kabir |
|
|
|
|
|
Schema Design