0

Referencing this great answer by PerformanceDBA:

The referenced answer approaches the problem of supertype subtype by :

  1. using a Discriminator in the base-type ( which could be constrained by using FK and lookup table for TYPE)

  2. creating a simple FUNCTION to check exclusivity which gets called using CHECK constraint in the subtype.

My question : if the discriminator is part of the primary key of the base-type and a User Defined Function is USED to check for exclusivity , would a design like this break any relational design or would it make the design simpler ( given that the PK is not multi valued of course) ?

Here is an example for illustration:

ProductID Description
120001 This product is Car ( starts with 120)
150001 This Product is Boat (starts with 150)
160001 This Product is Jet (starts with 160)

can call a function that checks for the ID of product and insert this into the desired table ( Jet table , Boat Table or Car table ) based on the ID?

if that is the case , can I get rid of the Discriminator column and the look up table too ?

is there any shortcoming of this approach ?

UPDATE to illustrate the two design approaches :

Illustration of Question

Ayed
  • 373
  • 5
  • 17
  • Does this answer your question? [SQL Server "pseudo/synthetic" composite Id(key)](https://stackoverflow.com/questions/33149354/sql-server-pseudo-synthetic-composite-idkey) – philipxy Feb 21 '23 at 11:29
  • 1
    1) As long as you stick to breaking 1NF, I can't help you, sorry.  2) Please read the **[Subtype](https://www.softwaregems.com.au/Documents/Article/Database/Relational%20Model/Subtype.pdf)** document. – PerformanceDBA Feb 28 '23 at 00:51
  • sorry , How am I breaking the 1NF in this case ? the ID is an atomic single /Value of the same data type of INT. please correct me if I am wrong. – Ayed Feb 28 '23 at 00:59
  • 1
    Read my Answer, carefully. "120" is one datum `ProductType`, "001" is a second datum `ProductNo`. "120001" is a combination, it breaks 1NF. *"the ID is an atomic single /Value"* is false, it is not atomic. – PerformanceDBA Feb 28 '23 at 01:10
  • thank you and please bare with me as I am trying to learn this , but what if this actually how the scooters are identified in real world ? there is a plate and a sticker that comes with the scooter it self that identifies it this way. I should have mentioned this earlier. – Ayed Feb 28 '23 at 01:15
  • 2
    The real world is physical, but the perception of the real world must, needs be, logical. If that is the plate, then the thing is not a `ScooterID`, but some form of serial number, which is made up of `ScooterType` plus `SerialNo`, each of which is an atom, the conglomeration of which is not an atom. – PerformanceDBA Feb 28 '23 at 01:32
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) An ERD is an image of DDL. Give a legend with an image. This site uses white & black background. So please no images with transparency. Please don't insert "EDIT"s/"UPDATE"s, just make your post the best presentation as of edit time. Put everything but only what is needed to ask your question in your post, not just at a link. Relate it to your question. Quote with credit & format as a quote. Please use standard spelling & punctuation. Clarify via edits, not comments. – philipxy Feb 28 '23 at 01:54
  • See [How do comment replies work?](https://meta.stackexchange.com/q/43019/266284) to learn to use `@x` to notify 1 non-sole non-poster commenter `x` per comment about that comment. Posters, sole commenters & followers of posts always get notified. Without `@` other commenters get no notification. – philipxy Feb 28 '23 at 01:55
  • @Ayed Please join me in a [Chat room](https://chat.stackoverflow.com/rooms/252187/scooter-serial-no) – PerformanceDBA Feb 28 '23 at 01:56

1 Answers1

1

is there any shortcoming of this approach ?

Yes.

You have broken 1NF:

First Normal Form
Each domain (column) must be Atomic, that is, indivisible
with regard to the SQL Platform (Datatypes).

Your ProductID is a conglomeration thing that is made up from two atomic things:
    ProductType CHAR(1) -- {C, B, J, ...}
and some number within that, say
    ProductNo INT.

At the point of your table (which is not described) in the data hierarchy, the PK is an ordinary Relational composite key:
    ( ProductType, ProductNo ).
not:
    ProductID.

Now you don't need a Subtype cluster in that table.  

But I suspect you need exclusivity pivoting on some other concept: if so, you need to post a model of all the tables related to this issue.

PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90