-1

I have Joined Tables Inheritance in my database : a Client table that can be a Company or a Person. I need to create an account for the client but only one if it's a person and one or many if it's a company. I also have users for backoffice that also need an account. So I tried this but I'm not sure if it's a good idea or not. So is it a good idea or not pls ? uml

Edited Thanks to The Impaler : enter image description here

Christophe
  • 68,716
  • 7
  • 72
  • 138
Stov
  • 29
  • 5
  • Two questions: 1) Is the Client object always abstract, always concrete, or any? I would guess "always concrete"; 2) Are the types exclusive? I would guess "yes". Depending on your answers the model can be modeled differently. – The Impaler Jan 18 '23 at 14:24
  • @TheImpaler Yes the class will be concrete, but what do you mean by "are the types exclusive"? – Stov Jan 18 '23 at 14:45
  • Some models allow a Client to have children on both sides (Company AND Person) at the same time, so they are "inclusive". Most of the time they should be exclusive: a client is either a company or a person, but not both. – The Impaler Jan 18 '23 at 14:57
  • Ok thanks for the explanation, so yes they will be exclusive – Stov Jan 18 '23 at 15:01
  • In your model the database is not enforcing exclusivity. A buggy app could create a client that is both a company and a person at the same time. You can make it exclusive by adding a `client_type` column in the Client table. and then use composite FKs in the child tables. – The Impaler Jan 18 '23 at 15:10
  • Also, the model is not enforcing clients to be concrete. A buggy app could insert a row in the Client table without any child in Company or Person. You can enforce "concrete inheritance" by adding FKs on the Client table that point to the Company and Person tables. – The Impaler Jan 18 '23 at 15:12
  • I guess that _three table_ means _tree table_? Actually I have no idea what a _three table inheritance_ would be. Alas your diagram does not show a single inheritance. – qwerty_so Jan 18 '23 at 19:57
  • @qwerty_so For me the "Three tables inheritance" is one of the three possibilities for implementing inheritance in the database : – Stov Jan 19 '23 at 08:37
  • @TheImpaler Sry I think I don't understand your 2nd comment. How the Client table can contains 2 Fks, pointing to the Company & Person, if the Client can be only 1 of the 2? – Stov Jan 19 '23 at 08:48
  • You should write that this way. Further (again) your diagram does not show any inheritance at all. – qwerty_so Jan 19 '23 at 09:34
  • 2
    @TheImpaler Two subtables may be either disjoint or overlapping. This is the terminology of UML, and it also corresponds to the math terminology. Your term "exclusive" is unclear/confusing. – Gerd Wagner Jan 19 '23 at 10:54
  • @user21034220 Your term "Three tables inheritance" is quite stupid and not a common term. Rather, in *Object Relational Mapping (ORM)* frameworks, the term *Joined Tables Inheritance (JTI)* is used. See, e.g., https://web-engineering.info/book/WebApp2/ch12s07.html#secInheritTbl – Gerd Wagner Jan 19 '23 at 11:00
  • @user21034220 Your UML class model is flawed: (1) through the multiplicity 1 at the sides of `Company` and `Person`, it states that each client must be associated both with a company and a person! (2) Since, in general, not every company and not every person is a client, you should either change the multiplicities of their association ends at `Client` to 0..1 or rename these classes/tables to `CompanyClient`and `PrivateClient`. – Gerd Wagner Jan 19 '23 at 11:49
  • @GerdWagner Thx for your comments. A Client is a Company or a Person (not none) and a Company/Person is a Client (not none). So, if I take the example of the last figure (12.12.A) in the doc you shared, the Client table has one PK (id) and no FK, and Company & Person both have one PK that also is an FK of the Client id ? – Stov Jan 19 '23 at 12:49
  • Right, and in your class model above, you have associations representing/implementing these two FKs. However, as I pointed out in my previous comment, you should change their multiplicities on the side of the subclasses (and you should rename these subclasses). – Gerd Wagner Jan 19 '23 at 15:48
  • @GerdWagner Noted with thanks. Over time I've forgotten that terminology, but I strongly perfer to use it in a strict way. Thank you for reminding me about that. – The Impaler Jan 19 '23 at 17:36
  • @user21034220 The Client table can be designed to have two FKs (one per child table) and but can enforce only one of them to be not null, by the use of a CHECK constraint. That way you'll enforce subclasses to be *disjoint* and also to be never abstract. – The Impaler Jan 19 '23 at 17:38
  • Underlined attributes mean static attributes in UML. – Christophe Jan 20 '23 at 06:27

1 Answers1

3

Your model is already pretty good, but you should fix/improve the following issues:

  1. By setting the multiplicity 1 at the sides of Company and Person, the current model states that each client must be associated both with a company and a person! You have to fix this by changing the multiplicity to 0..1 and possibly add an invariant that requires each client to be either a company or a person (in a constraint box attached to the class rectangle).
  2. Since, in general, not every company and not every person is a client, you should either change the multiplicities of their association ends at the Client side to 0..1 or rename these classes/tables to CompanyClient and PrivateClient.
  3. In UML, the names of classes should not be underlined.
  4. In UML, the standard ID attribute(s) of a class (called primary key in databases) have to be desginated by "{id}" appended at the end of the attribute declaration line, and not by underlining the attribute(s) concerned.
  5. It's not good practice in UML, to redundantly represent reference properties both with associations and in the form of attributes, as you do with your #-prefixed attributes. Just drop these non-UML syntax attribute declaration lines.
  6. Better rename your AccountCompany class to AccountsByCompany.
Gerd Wagner
  • 5,481
  • 1
  • 22
  • 41
  • Do you have an example, or documentation, of how to add this invariant for Client table please? – Stov Jan 20 '23 at 10:25
  • 1
    As explained on https://www.uml-diagrams.org/constraint.html?context=class-diagrams, you could either add the constraint as a dashed line between the two associations (`Client`-`Company` and `Client`-`Person`) labeled by "{xor}", or you could attach a a note symbol to the `Client` class rectangle, in which you place the constraint as plain text (e.g., "A client may be either associated with a company or with a person."). – Gerd Wagner Jan 24 '23 at 19:54