DB design

Question

DB design

Sue-651097

Say Hey Kid

Points: 675
More actions
February 15, 2009 at 4:05 am

#218471

hello,
i have to design an address database which provides several languages for each entry. there are some tables like title, contact, description..
create table tbltitle(
idtitle int identity(1,1) primary key,
)
create table tbltitlelang(
fktitle int, -- foreign key
fklanguage int, --foreign key
title varchar(50)
)
i need one id for a title which is then refered in several languages in the titlelang table.
how can i make an entry in the title table to create an identity value?
there are about 20 tables like the title table. is it a good idea to make an extra table for each table/language combination (which all have the same attributes) or can i integrate all entries in just one table (problem, that several entity typs are mixed in one table..?).
please give a hint 🙂
thx,
sue
Susanne

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I think you are planning the right way. I try to analyze the issue like this:

say i was going to support Spanish language version of my data, and consider adding one column to the main table to point to the Language Links. Is there a possibility that i would support another language or two in the future? as soon as I answer yes to that, I see that the one-to-many table you are planning to add is better....since it supports an unlimited or unknown number. If I had tunnel vision, and I was only planning to support one other language, I'd probably just add one column.

Your one to many table is definitely how I would do it. just give it it's own primary key:

create table tbltitlelang(

titlelangid int identity(1,1) not null primary key,

fktitle int, -- foreign key

fklanguage int, --foreign key

title varchar(50)

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sue-651097 Say Hey Kid Points: 675 More actions · Answer 2

thank you. it seems that i didn't mention clearly what i mean.

there is a tabel with addresses. for each address i can refer to a title, a contact, martialstatus, ... in a foreign key field in the address tabel.

now i have several lookup tables for title, a contact, martialstatus,..

but these entries have to be provided in several languages

version 1:

tbladdress:

id fkcontact firstname

1 1 mary

tblcontact:

idcontact

1

tblcontactlanguage:

fkcontact fklanguage contact

1 1 misses

1 2 madame

1 3 frau

so i can refer to the contact in every language.

now the question:

in the db i need about 20 lookuptables with multilanguage entries.

the contactlanguage tabel has the same attributes like the martialstatuslanguage table or the titlelanguage table. is it better to create one table for each entitytyp or is it the better way to create just ONE tabel for xxx/language like this:

Version 2:

tbladdress:

id fkcontact firstname

1 1 mary

tblcontact:

idcontact fktext

1 1

tbltext:

idtext

1

2

3

tbltextlanguage:

fktext fklanguage text

1 1 misses

1 2 madame

1 3 frau

2 1 MBA

in this solution i have only one table for all different entitytypes which means one more join but less tables.

glad about any help,

Susanne

Oscar D. Garcia SSC Eights! Points: 874 More actions · Answer 3

Let me try to understand your requirements. In your database, there are many labels that can be used, and these labels can be translated in multiple languages.

You could use a single mapping table to associate the translated values to your label:

So a many-2-many table could hold the FKs to the label_id and language_id plus the actual translated text.

If the label_id is not unique( i.e. title_id and status_id could both be 100), you then need to create a parent_id column. This will be used to identify/map your groups or types:

I hope this helps.

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 4

[font="Verdana"]Hmmm. I've never found a satisfactory answer to this question.

On one hand, you end up with strongly typed tables to which referential integrity can easily be established. But you end up with lots of little tables.

On the other hand, you end up with one generic lookup table (or two in your scenario), but it becomes a little harder to maintain referential integrity.

I have to admit I've designed databases both ways, but I tend to veer towards the first option. I don't really think that there's a right or wrong solution here, although the purists will say use the first form.

In practice, I've used the rule "store different things in different boxes". So don't store different things in the same column, for example. "Oh, depending on whether this other field is a yes or no, this field here can contain a code or a name." Or "depending on the type, this field contains an foreign key to one of tables A, B or C." As a design rule, avoiding this is a good idea.

But when it comes to codes... I mean, a lookup code is a lookup code. Who really cares what it's for? They are all the same thing. So storing them in the same table makes sense.

And yet, often, they do have different categories. So storing them in separate tables also makes sense.

Excuse me while I step outside and have words with myself... :w00t:

[/font]

Sue-651097 Say Hey Kid Points: 675 More actions · Answer 5

that are exactly the things which are going around my head ;))

but as u think that both design are practicable - that helps. then i tend to implement the version with just one translation table.

Susanne