Shared primary key

Question

I would suggest that this is a semi-community question, but I cannot find it in the list of past questions. I have a set of tables for products that need to split the primary key index. Assume the following:

product1_table: id, name, category, ...other fields product2_table: id, name, category, ...other fields product_to_category_table: product_id, category_id 

Obviously, it would be useful to have a common index between the two product tables. Note: the idea is to keep them separate because they have basically different sets of fields outside the basics, however they have a common classification.

UPDATE:

Many people have proposed table inheritance (or gen-spec). This is an option that I know of, but provided on other database systems. I could share the sequence between the tables. I was hoping MySQL has a similar solution. I guess this is not based on answers. I think I have to go with table inheritance ... Thanks to everyone.

Answer 1

This is not very common, no. There is no native way to share a primary key. What could I do in your situation:

 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 table of main products in which there are entries for all products, and fields that are generalized between types, and tables with foreign keys in the table of main products that have type data.

Answer 2

The best design is to place common columns in one product table and special columns in two separate tables. Use product_id as the primary key in all three tables, but in two special tables this is also the foreign key back to the main products table.

This makes it easy to find the base product for identifiers and names by category.

Please note that your design allows each product to be in one category no more.

Answer 3

You seem to be looking for table inheritance.

You can use a common product table with attributes common to product1 and product2, plus a type attribute, which can be either "product2" or "product1"

Then the tables product1 and product2 will have all their specific attributes and a link to the parent table product .

 product: id, name, category, type product1_table: id, #product_id, product1_specific_fields product2_table: id, #product_id, product2_specific_fields 

Answer 4

First let me state that I agree with everything that Chaos, Larry and Phil said.

But if you insist otherwise ...

There are two reasons for your overall PK. One uniqueness in two tables and two for complete referential integrity.

I'm not sure what exactly the "sequence" supports Auto_increment columns. It seems that there is a system setting to determine the growth by value, but nothing per column.

What would I do in Oracle, just split the same sequence between two tables. Another method would be to set the STEP 2 value to auto_increment and start one at 1 and the other at 2. In any case, you generate unique values ​​between them.

You can create a third table in which there is nothing but a PK column. This column can also provide Autonumbering if there is no way to create a missed autodial inside a single server. Then, on each of your data tables, you add CRUD triggers. Insertion into any data table first initiates insertion into the pseudo-index table (and returns the identifier for use in the local table). Similarly, deletion from the local table initiates deletion of pseudo indexes from the table. Any table of children that should point to the parent points to this pseudo-index table.

Please note that this should be a trigger for each row and will slow down the work in these tables. But product type tables, as a rule, do not have very high DML speeds. Anyone who complains about "performance impact" is not given the scale.

Please note that this is provided as a valid alternative, not my recommendation as the best way.

Answer 5

You cannot "share" with the primary key.

Not knowing all the details, my best advice is to combine the tables into one product table. Having optional fields filled out for some products, and not others, is not necessarily a bad design.

Another option is to have an inheritance model in which you have one product table and then two product subtype tables that reference the main product table and have their own specialized set of fields. Querying this model is more painful than a single IMHO table, so I find it less desirable.

Answer 6

Your explanation is a little vague, but based on my basic understanding, I would have a desire to do so

Product table contains common fields

 product ------- product_id name ... 

the product_extra1 table and the product_extra2 table contain different fields in these tables a one-to-one relationship is applied between product.product_id and product_extra1.product_id, etc. Apply the one-to-one relationship by setting product_id in the foreign key tables (product_extra1, etc.) to be unique, using a unique constraint. You will need to define business rules on how this data is populated.

 product_extra1 --------------- product_id extra_field1 extra_field2 .... product_extra2 --------------- product_id different_extra_field1 different_extra_field2 .... 

Based on what you have above, the product_category table is an intersecting table (from 1 to many - from several to 1), which implies that each product can be associated with many categories. Now it can remain unchanged.

Answer 7

This is another case of gen-spec.

See previous discussion