field naming question?

[Brian McCullough]

Hello all...

I have an application that needs to be able to associate a name with a
variation of the name. For example Elizabeth vs Liz/Beth.

I was thinking of building two tables:

GivenName
GivenNameVariation

The columns in the GivenName table are pretty straight forward:
GivenNameId autonumber,PK
GivenName char(35)

I was thinking of making the following columns in the GivenNameVariation
table:
GivenNameId int
GivenNameVariationId int

-together they would make up the primary key of the GivenNameVariation
table.

The problem is that I do not want to confuse other developers who might look
at this and see the GivenNameVariationId column and think that this could be
the Primary Key....

What is the *appropriate* naming convention to use when you have a table
with two columns that refer to the same column in another table?

Thanks!!!

Brian

 

[Tim Ferguson]

Hello all...

I have an application that needs to be able to associate a name with a
variation of the name. For example Elizabeth vs Liz/Beth.

I was thinking of building two tables:

Isn't it a many-to-many relationship? Sam can belong to Samuel or
Samantha; Samuel can degenerate into Sammy, Sammo or Sam; and so on.

I was thinking of making the following columns in the
GivenNameVariation table:
GivenNameId int
GivenNameVariationId int

Actually, I'd probably dump all the autonumber stuff, and have one table
like this:

FullName ShortName
======== =========
Samuel Sam
Samuel Sammy
Samuel Sammo
Samuel Mule
Samantha Sam
Samantha Samba

and so on. The PK would be both columns together. Without benchmarking
it, I am not sure if the disadvantage of keying on a shortish text field
would outweigh the advantage of doing away with all those extra joins and
FK checks: it probably depends on the application. I am sure, however,
that the simpler design will save mountains of maintenance.

The problem is that I do not want to confuse other developers who
might look at this

Surely your follow-on developers will be able to look at your neatly
indexed and cross referenced documentation! This information will be held
repeatedly in the Entity-Data Store correspondence diagrams, the Entity

What is the *appropriate* naming convention to use when you have a
table with two columns that refer to the same column in another table?

To be honest, I _always_ use field names that make sense in the context
of the entity that the table is modelling. If it's a father, the field is
called Father (even if the PK on the other end is called PersonID). If
it's the FormalVersion of a nickname, then that is what it gets called.
Think of what the SQL is going to look like later:

SELECT CosyName FROM NameVariations
WHERE FormalVersion = "Tabatha"

Doesn't that make sense?

HTH


Tim F