Mapping and Inserting Values into a Specialized table

Question

For a system i'm designing,i have one parent entity,Computer

Computer(computer_id,computer_processor,computer_hddrive,computer_memory);

A computer can be of two types,Desktop and Laptop,each having their own specific attributes.

Laptop(laptop_id(Foreign key of computer_id),laptop_color,laptop_battery);

and similarly a table for Desktop.

Now the entries to these tables is via a end-user form,my main question is, what would the exact INSERT queries be for adding say to the inventory,a laptop if i were to have 3 tables,one superclass and two subclass tables? I understand i would first how to make an entry to the computer table and then laptop,but how will this be carried out automatically?

Answer 1

It's not always carried on automatically. Depends on the ORM you are using (if any ) on the app side it may or may not be implemented . For instance, it works for Hiberanate (personally I use @Inheritance(strategy=InheritanceType.JOINED) in main entity).

If you don't use any, then you need to do carry PK of main entity yourself - again, depends on RDMS engine, you can use RETURNING (Oracle, Postgres) or OUTPUT (SQLServer ) or some form of LAST_INSERT_ID (mysql, and maybe others ) to get value of auto-generated primary key in master table.

Also, as a side note I would recommend to have discriminator column in all tables (for instance, computer_type_id ) to ensure computer can have exactly one sub-type . E.g.

computer : Computer(computer_id, computer_type_id (FK to computer_type), PK (computer_id), UNIQUE(computer_id,computer_type_id)....)

laptop : Laptop (computer_id, computer_type_id DEFAULT LAPTOP_TYPE_ID, FK (computer_id, computer_type_id), CHECK (computer_type_id = LAPTOP_TYPE_ID))

Note : Unique constraint on (computer_id,computer_type_id) may see redundant (and it is from pure theoretical point of view), but it's added for a reason - thus you can refer to it in child tables. In some RDMS , for example Oracle, both constraints (PK and UNIQUE) can share the same index , so no overhead from performance point of view...