Database Design: Storing the primary key as a separate field in the same table

Question

Hi,

I have a table that must reference another record, but of the same table. Here's an example:

Customer
********
ID
ManagerID (the ID of another customer)
...

I have a bad feeling about doing this. My other idea was to just have a separate table that just stored the relationship.

CustomerRelationship
***************
ID
CustomerID
ManagerID

I feel I may be over complicating such a trivial thing however, I would like to get some idea's on the best approach for this particular scenario?

Thanks.

Answer 1

As a programmer I like the first approach. I like to have less number of tables. Here we are not even talking of normalization and why do we need more tables? That is just me.

Answer 2

There's nothing wrong about the first design. The second one, where you have an 'intermediate' table, is used for many-to-many relationships, which i don't think is yours.

BTW, that intermediate table wouldn't have and ID of its own.

Answer 3

Why do you have a "bad feeling" about this? It's perfectly acceptable for a table to reference its own primary key. Introducing a secondary table only increases the complexity of your queries and negatively impacts performance.

Answer 4

Can a Customer have multiple managers? If so, then you need a separate table. Otherwise, a single table is fine.

Answer 5

You can use the first approach. See also Using Self-Joins

Answer 6

Follow the KISS principle here: Keep it simple, (silly | stupid | stud | [whatever epithet starting with S you prefer]). Go with one table, unless you have a reason to need more.

Note that if the one-to-many/many-to-many relationship ends up being the case, you can extract the existing column into a table of its own, and fill in the new entries at that time.

Answer 7

There's absolutely nothing wrong with the first approach, in fact Oracle has included the 'CONNECT BY' extension to SQL since at least version 6 which is intended to directly support this type of hierarchical structure (and possibly makes Oracle worth considering as your database if you are going to be doing a lot of this).

You'll need self-joins in databases which don't have something analogous, but that's also a perfectly fine and standard solution.

Answer 8

The only reason I would ever recommend avoiding such self-referecing tables is that SQL Server does have a few spots where there are limitations with self-referencing tables.

For one, if you ever happen to come across the need for an indexed view, then you'd find out that if one of the tables used in a view definition is indeed self-referencing, you won't be able to create a clustered index on your view :-(

But apart from that - the design per se is sound and absolutely valid - go for it! I always like to keep things as simple as possible (but no simpler than that).

Marc