Before posting, I have looked for a good solution on internet and I have coined this post for 3 days in row and trying to think through it prior this post. So if I have missed something or got it wrong, please, have understanding.
I need to model Invoicing Database for creating Invoices (in PDF) with SQL Server 2014 and Visual Studio 2013. My company operates and works only with other businesses, that is business entities.
I don't know how to model situation in which:
Seller (Service Provider) issues Invoice to Customer XOR Representative. (XOR = Exclusive OR)
I need to track info about companies and all of them: Seller, Customer or Representative initially have the same attributes. I can come up with identifiers for them like SellerID, CustomerID, RepresentativeID but I don't know if it is valid thing to do or not, since they inherit ID's from Superclass (Company) and up to this point I can't think of any other unique attribute.
There are some business rules like:
- Seller (only one at the moment - my company) issues Invoices to either Customer or Representative.
- Customer can have only one or none Representative and one Representative can represent many Customers.
- Representative is Customer and is Company
- I need to track info about issued Invoices and Companies, of course there are other entities like: Bank Accounts, Invoice Items, Services, Agreements (which can be Contracts and Orders)
Here are the Entities:
- Company (supertype)
- Seller (Service Provider) (subtype of Company)
- Customer (subtype of Company)
- Representative (subtype of Customer or subtype of Company - see pictures for more on this...) (maybe this should be recursive relationship)
- Is supertype / subtype needed here (since subtype Entities have no unique attributes and since it gives me a headaches)?
- How to implement inheritance with one entity in recursive relationship - if for instance Customer / Representative is in recursive relationship (relational table example needed)
- Can you give me relational solution (EER to relational) with some examples (like table with data) so I can wrap my mind around it.
I have come up with following EER Model:
And now I am missing one of following two modeling options (some other suggestion that will work better and be less newbie friendly) ...
This one is full_disjoint with more inheritance and partial disjoint:
And this one is full overlapping (missing one "p" on a picture description):