3

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)

Questions:

  1. Is supertype / subtype needed here (since subtype Entities have no unique attributes and since it gives me a headaches)?
  2. How to implement inheritance with one entity in recursive relationship - if for instance Customer / Representative is in recursive relationship (relational table example needed)
  3. 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:

eer_model_missing_representative

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:

full_disjoint_partial_disjoint

And this one is full overlapping (missing one "p" on a picture description):

full_overlapping

  • 1
    Why Customer / Representative needs to be a recursive relationship? Can a representative delegate the representation to another representative? – ypercubeᵀᴹ Apr 16 '15 at 11:15
  • No delegation at this point. It is not common for our business. And it does not have to be a recursive relationship, I am actually proposing some ideas to solve this and asking for new ones at same time. But thing is I can't wrap my mind about translation to relational model... and implementing it to MS SQL server – Zlatko Alomerovic Apr 16 '15 at 11:20
  • 1
    When you said "recursive relationship" did you mean "reflexive relationship"? – Walter Mitty Apr 17 '15 at 10:29
2

I want to call attention to one of your business rules: Representative is Customer and is Company.

This sounds like a superclass/subclass situation to me, and I think you have the EER diagram well in hand, as far as ER modeling goes. Where things start to get interesting (and a little messy) is when you try to switch over from an ER model (which is abstract and implementation neutral) to a relational model (which is not).

At this point, you'll have to choose between a few well known techniques that make up for the fact that the relational model doesn't incorporate inheritance. You may wish to include this tag in your original question:

Here are three of the techniques in a nutshell: single table inheritance, class table inheritance, and shared primary key.

Single table inheritance lumps all the subclasses together in one table. Data that does not pertain to a given instance is left NULL.

Class table inheritance has one table for the superclass and one table for each subclass. Generalized data goes in the superclass table, and specialized data goes in the appropriate subclass table.

Shared primary key enforces the one to one nature of the relationships between each subclass and the superclass. It also allows a foreign key elsewhere in the database to reference the superclass without knowing or caring which subclass it belongs to.

For a good treatment of this subject, look up Martin Fowler's treatment on the web.

Edit based on feedback

The question of which ER model is "correct" is usually determined by the database design team by analyzing the subject matter, and coming up with a model that depicts the way it works in "the real world". This presumes that the database is yet to be designed, but the business itself has already been designed, and the business design is not to be changed. Your comment makes me think that you are designing (or redesigning) the business itself, not just the database. If so, then the question is outside the expertise of database design.

To deal more specifically with the questions you asked:

  1. Is supertype / subtype needed here (since subtype Entities have no unique attributes and since it gives me a headaches)

If there are no attributes that are specialized to the subtypes , then no inheritance (from a data perspective) is needed. So it won't make any difference to the relational design. Don't give yourself headaches. Pick a model that works for your business, and let it go.

  1. How to implement inheritance with one entity in recursive relationship - if for instance Customer / Representative is in recursive relationship (relational table example needed)

If Customer and Representative are the same entity, give them a common name. One entity with two names is confusing. If they are different entities, rephrase your question. From my answer to question 1, why is implementing inheritance called for?

Are "recursive relationship" and "reflexive relationship" the same thing? If not, please clarify your question.

  1. Can you give me relational solution (EER to relational) with some examples (like table with data) so I can wrap my mind around it.

That's what I thought I was answering the first time. Fowler's treatment of inheritance/relational issues is available on the web. Here and here. His examples are about players and cricketers, not customers and representatives, but the issues concerning implementing inheritance (really pseudo inheritance) in a relational system are not case dependent.

  • Thanks for your answer. I have an idea on how to translate it to relational. BUT, I asked for some solution that is, let's say, industrial solution that is checked and verified through use cases. There is actually very good introduction about EER Modeling and translation to Relational Model from Brian Finnegan on Youtube: EER to Relational Translation Techniques. Guess I'll have to check it my self then :). – Zlatko Alomerovic Apr 17 '15 at 8:36
  • OK, I didn't get that from my reading of your question. I'll go back and edit my response. – Walter Mitty Apr 17 '15 at 10:30
  • Unfortunately, when I did a google search on "reflexive relationship", what I got was a bunch of hits on "reflexive relation". This is a completely different concept, even though the names are almost identical. – Walter Mitty Apr 17 '15 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.