Relationship Design

[robmkimmons]

In creating a glossary I have tblTerm. A term might have one or more aliases that are also terms within the same table.

For example:

Term X = Aliases Y & Z
Term Y = Aliases X & Z
Term Z = Aliases X & Y
Term A = No Alias
Term B = Alias C
Term C = Alias B

My current table structure:

tblTerm
TermID (autonum)
TermName (txt)

I am confused as to how i should proceed. Should I create a relationship between tblTerm and itself? ..if so, how? Or should I create a tblAlias?

Thanks!

~Rob

If we expect the unexpected, does that make the unexpected... well, expected?

 

[PHV]

You have a many-to-many relation, so you need a junction (aka bridge) table, eg:
tblAlias
TermID (Long, ForeignKey referencing tblTerm)
AliasID (Long, ForeignKey referencing tblTerm)
PrimaryKey(TermID,AliasID)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robmkimmons]

Thank you for your response.

So that I understand correctly, tblAlias will only actually have TWO fields, each a foreign key and combined together making a composite key for that table?

Also, tblAlias.AliasID will reference tblTerm.TermID?

Thanks for your help!

~Rob

If we expect the unexpected, does that make the unexpected... well, expected?