Shared Primary Key

Question

I would guess this is a semi-common question but I can't find it in the list of past questions. I have a set of tables for products which need to share a primary key index. Assume something like the following:

product1_table:
    id,
    name,
    category,
    ...other fields

product2_table:
    id,
    name,
    category,
    ...other fields

product_to_category_table:
    product_id,
    category_id

Clearly it would be useful to have a shared index between the two product tables. Note, the idea of keeping them separate is because they have largely different sets of fields beyond the basics, however they share a common categorization.

UPDATE:

A lot of people have suggested table inheritance (or gen-spec). This is an option I'm aware of but given in other database systems I could share a sequence between tables I was hoping MySQL had a similar solution. I shall assume it doesn't based on the responses. I guess I'll have to go with table inheritance... Thank you all.

Answer 1

It's not really common, no. There is no native way to share a primary key. What I might do in your situation is this:

product_table
    id
    name
    category
    general_fields...

product_type1_table:
    id
    product_id
    product_type1_fields...

product_type2_table:
    id
    product_id
    product_type2_fields...

product_to_category_table:
    product_id
    category_id

That is, there is one master product table that has entries for all products and has the fields that generalize between the types, and type-specified tables with foreign keys into the master product table, which have the type-specific data.

Answer 2

A better design is to put the common columns in one products table, and the special columns in two separate tables. Use the product_id as the primary key in all three tables, but in the two special tables it is, in addition, a foreign key back to the main products table.

This simplifies the basic product search for ids and names by category.

Note, also that your design allows each product to be in one category at most.