3

Consider an accounting system as an example. I have an Entity called Client. Client can be of different types, with different fields applicable to different types. I consider creating separate tables for different types of Client, each having fields applicable to the respective type and have one master table referencing all of them and have fields applicable to all types.

Currently, I come up with the following design:

enter image description here

But I don't think my design is efficient enough (or even correct and free of errors). What would you suggest? Also, if this is important in any way, I am planning to utilize MariaDB.

Improve this question
0

3 Answers 3

Reset to default
2

Sorry, but is an entrepreneur not also an individual? You should record the name etc. in individual, then the additional info in another table. Now you may have one person double. And stuff like address should be linked to "entity" - not even client, you may end up having suppliers there too.

The "Data Model Resource Book" has perfect diagrams for address and contact handling including relationships of entities with each other. There is a lot of detail issues you get seriously wrong in your approach.

Improve this answer
0

Your design looks right to me. It's really a case of a design pattern called Class Table Inheritance. Your class is Client, and your subclasses are Client_individual, Client_entrepreneur, and Client_legal_entity.

You might benefit by using a technique called Shared Primary key. The idea behind shared primary key is that the PK of each subclass table is a copy of the PK from the superclass table.

This way instead of having each subclass (subtype) table having its own id field, the class-id field can do double duty as a primary key and as a foreign key. This has two advantages: it will enforce the one-to-one nature of the class-subclass relationship, and it will in general speed up your joins.

I question whether address is an attribute of only a client_individual, or whether all types of clients have an address. You know your subject matter better than I do.

Improve this answer
1
  • It looks pretty wrong to me - done simplistic. Right the same way someone not in the business may think it is easy to put a man on the moon, while those building rockets make things different.
    – TomTom
    Commented Jun 3, 2013 at 4:09
-1

I've designed a system like this before in MySQL. I was tracking friends across multiple social networks. What I think is best for you (works great for me) is to track clients by an enum and their Id. Since MySQL can't do dual foreign key constraints, you have to maintain this relationship in your application. The client_type_id and client_type should be removed and replaced with a simple enum. This is more efficient

Improve this answer
3
  • What is a "dual foreign key constraint"?
    – ypercubeᵀᴹ
    Commented Apr 3, 2013 at 6:15
  • I misspoke. I probably should have used the term "dynamic foreign key constraint" ie where an Id must exist in one of four tables depending on the value of an enum. (MySql doesn't like this, it limits foreign keys to a single table, and only with one column at a time) Because this type of hypothetical relationship involves two columns instead of one, my brain went to "dual"
    – benathon
    Commented Apr 3, 2013 at 13:04
  • Are there any DBMS that allow this weird FKs to multiple tables? About the one-column limitation you are wrong, MySQL allows multi-column FKs.
    – ypercubeᵀᴹ
    Commented Apr 3, 2013 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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