Hello,

Let's say I have a table that contains three foreign keys to other tables in the database. Together, all three could uniquely identify the row. Is it better practice to make those three columns the primary key, or leave them as foreign keys and add an identity column?

Thanks!

For practical reasons, I create all tables with a single column PK. Joining tables is much easier if you only need to have one column to join on. Second, if you are processing a set of entities its is easier to keep track of where you are if you only need to compare on a single column (and many UI components assume your table has a single column PK).

On the other side, when you are mostly interested in browsing the table using only one of two of the foreign keys, you will end up with a table that is never accessed by the PK.

Kristian wrote:

Hello,

Let's say I have a table that contains three foreign keys to other tables in the database. Together, all three could uniquely identify the row. Is it better practice to make those three columns the primary key, or leave them as foreign keys and add an identity column?

Thanks!

As WvNoort says, it can be both, depends on what you like. There is no rule for this, one camp says: "one PK field per table/entity!", while the other camp (admitted, I'm that camp ) says: "one PK field per entity, unless the entity is an objectified relation, then the entity should be identified by the combination of the FKs. ". But it's a pure semantical issue, they both have advantages and disadvantages.

Otis wrote:

Kristian wrote:

Hello,

Let's say I have a table that contains three foreign keys to other tables in the database. Together, all three could uniquely identify the row. Is it better practice to make those three columns the primary key, or leave them as foreign keys and add an identity column?

Thanks!

As WvNoort says, it can be both, depends on what you like. There is no rule for this, one camp says: "one PK field per table/entity!", while the other camp (admitted, I'm that camp ) says: "one PK field per entity, unless the entity is an objectified relation, then the entity should be identified by the combination of the FKs. ". But it's a pure semantical issue, they both have advantages and disadvantages.

So, to make sure I understand "objectified relation", this is the linking (join) table used in many-to-many relationships?

Kristian wrote:

Otis wrote:

Kristian wrote:

Hello,

Let's say I have a table that contains three foreign keys to other tables in the database. Together, all three could uniquely identify the row. Is it better practice to make those three columns the primary key, or leave them as foreign keys and add an identity column?

Thanks!

As WvNoort says, it can be both, depends on what you like. There is no rule for this, one camp says: "one PK field per table/entity!", while the other camp (admitted, I'm that camp ) says: "one PK field per entity, unless the entity is an objectified relation, then the entity should be identified by the combination of the FKs. ". But it's a pure semantical issue, they both have advantages and disadvantages.

So, to make sure I understand "objectified relation", this is the linking (join) table used in many-to-many relationships?

A relation can be m:n, like Order - Product, and the relation itself is objectified, as it has attributes for itself/relations to other entities. See: http://www.orm.net/pdf/ORMwhitePaper.pdf , page 10, figure 8. or http://www.orm.net/pdf/springer.pdf, page 4