I need some help please. Party is the supertype to person, dealer and customer. I am having a difficult time modelling the customer and dealer tables.

This system can have many dealers and a dealer can have many customers. What I need is for every dealer to keep a sequential list of its customer numbers. Sample data might look like this.

Since a subtype cannot contain any other PK other than the PK from the supertype I thought about adding attributes such as customer_id in the customer table and dealer_id in the dealer table and making them alternate keys. I could then link to the dealer_customer table using the AKs.

I tried this but it does not give me my sample data above because the db is enforcing the constraint on the customer_id by not allowing me to duplicate.

Is there another way to do this?

Thanks.

Frank

On the odd chance that a customer is fully dependant on dealer (meaning that you can't have a customer without a dealer, that the dealer is in fact an identifying characteristic of the customer), then make the customer PK a composite of dealerId and customerId.

If the customer is an independant entity (meaning that a customer can exist without a dealer, and that a customer can be transferred from one dealer to another), then the only way I know to do what you've described is to create a many-to-many linking table that joins dealers to their customers and put the dealer-relative id in that linking table. This still won't cope with deletions of the link between dealer and customer (which would leave "holes" in your numbering), but it is the closest that I can get.

-PatP

Thanks for the explanation Pat. I have taken your first approach which still left holes in the customer number. I added another attribute for the customer_no in the customer table which works fine. Whether it is correct or not, I don't know.

Frank