0

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?

0

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...

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.