Problem: Design a ER diagram such as:
- An item has the attribute: description.
- An item can be sold by a company or a person.
- A person has the attributes: name, phone and email.
- A company has the attributes: company name, address and a contact person who is one from the person entity set.
- A contact person cannot sell the same item with the company he works for.
This is my design:
I'm learning and I know this is a stupid question. But please correct and point out what are wrong in my design.
- I'm not sure that I should remove the primary key SellerID in Seller entity and add companyID to Company entity and personID entity.
- Is the Contact Person entity connected to Person entity correctly?
- How can I demonstrate the constraint: a contact person cannot sell the items (distinguished by item ID) his company is selling.
Thank you.
Best Answer
As far as primary keys, ALL TABLES HAVE THEM. It's a little over the top, but I've seen too many databases that lose their data integrity because the developers didn't want to lock down a PK ID field off the start. I would have a table for persons and a table for companies, with an attribute field to mark them as a seller.
For the company point of contact, just make it a foreign key relationship to the persons table (but only there will only ever be one POC). If there could be more than one POC, then make a join table between the two. Graphically, you look pretty good there from what I can tell. If I wanted to nit-pick, I would make Point Of Contact an attribute of the Company Table, with a "IS A" link to the Persons Table.
Your last question gets into a gray area of DB Design. You're asking about a "Business Rule." The best approach to this is to have something in the application to check that the Seller_ID does not match a Company_ID. It would probably end up being a bit of logic in the stored procedures. Trying to design that into the database any other way won't be easy, or maintainable.