Having trouble working with subtypes in MySQL

Question

I'm having some trouble with using subtypes/supertypes in MySQL.

I use C# .NET to make an administration system for my local club, and I have some trouble making a nice database.

My database looks like this:

supertype:

========
Product
---------
Prod_id (PK)
Name
Barcode

and the subtype:

=======
Card
--------
Prod_id (FK)
Price

I have 3 subtypes, Cards, Weapons, Ammunition which are basically the same thing (though I store different things in them).

Maybe I'm overcomplicating things, but all I want to do is, when I create a new card, that a product is automatically generated (I use a trigger for that), and that the Cards.Prod_id gets set to the newly generated Product.Prod_Id.

How do I do this?

Is this the right way of using subtypes, or am I doing something completely wrong?

Answer 1

You may use trigger. But, there is another, I think better way:

• Alter Product table - add AUTO_INCREMENT property to the primary key.
• Insert new product, MySQL will generate new ID
• Get last inserted ID using last_insert_id MySQL function, and insert new record in child table

MySQL example:

CREATE TABLE Product(
Prod_id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(40),
Barcode INT
);

CREATE TABLE Card(
Prod_id INT,
Price DECIMAL(8, 2),
FOREIGN KEY (Prod_id) REFERENCES Product(Prod_id)
);

INSERT INTO Product VALUES (NULL, 'phone', 1000);
INSERT INTO Card VALUES(LAST_INSERT_ID(), 10.5);

First INSERT wall add new product with autogenerated ID. The second, will get this new ID and use it for new Card record.

Using AUTO_INCREMENT