design question

What would the best / most correct way be to implement a relationship where you have for example a customer table, a partner table and a orders table and both customers and partners can have orders associated with them. This is just an easy way for me to describe the relationship I am looking at and is not really the data sets I am working with.

Following this analogy I currently have customers and orders and the orders table has a column customer_id to link each order to a customer. I now want partners to start to be able to place orders. It does not seem logical to me to have a second order table for them but the two identity columns that are the id columns would be on separate tables and thus could conflict. Only thing I can think of is to start the partner id identity column at a really high number. Is this the right thing to do it somehow does not feel right.

I would suggest adding another column to the Orders table called partnerId and FK it to the Partners table. Place a check constraint on the table to ensure that one of these columns is null at all times. There will be no conflict with the ids. For display purposes, you can check to see which column is not null and display the header Customer or partner in the order so people reading it are not confused. Or even just keep saying Customer id but prefix the display of partnerIds with a P. This seems to be the cleanest solution in my opinion.

|||

I would consider partners a special type of customer, rather than them being two different things. Then for each partner, you create an entry in the customers table (perhaps with a customertype field). Then for those customers that are partners, you can add a row to the partner table that uses the customer id as it's own primary key.

Another option is to create a "Entity" table of some kind that is the one making the orders, and partners and customers point to the entity (are specific types of entity). Of course you can use a different name if you want (Persons, BusinessEntity, etc).

These types of designs will allow you to reuse code, and perhaps even some UI savings when dealing with information that is shared between the two types of entities (Name, address, phone number, etc).