5

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.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
Endophage
  • 21,038
  • 13
  • 59
  • 90
  • Does mySql have sequences or Autonumber columns? – Stephanie Page Jan 25 '11 at 17:16
  • Yes, the usual practice for primary keys is an `auto_increment` integer column. – chaos Jan 25 '11 at 17:17
  • Do the Auto_increment columns accept any sort of parameters like START or STEP? – Stephanie Page Jan 25 '11 at 17:42
  • No `STEP`. You can set a start value. I think I see where you're going with this -- partition the PK space so each table has its own allocation. Nasty hack, but it should work. – chaos Jan 25 '11 at 18:31
  • There is no such thing as "shared" primary key. It is an ordinary Supertype-Subtype structure; the relation is 1::1; the PKs in the Subtypes are also FKs to the Supertype; in which case the PKs are the **same**, not "shared" (see Larry's answer). There is no need for **additional** ids and **additional** indices in the type-tables, unless you want to slow you tables down. – PerformanceDBA Jan 26 '11 at 12:16
  • @Endophage: Is it possible you are confusing two separate but related issues? The first is sharing a _sequence_ of numbers between two tables. You can do this in many databases if they generate sequences. You could simulate it in MySQL by inserting and deleting rows from a table to produce sequence numbers. But, once you use those numbers as primary keys in _two different_ tables there's no way to establish a single primary key constraint that would generate a single index with values from both tables and check foreign keys against them. – Larry Lustig Jan 26 '11 at 13:54
  • @Larry Lustig They're MyISAM tables so there are no foreign key constraints anyway. Which also relates to your comment below. – Endophage Jan 26 '11 at 15:49
  • @Endophage: If you're not actually using relational integrity constraints then you can come fairly close to what you wanted to do in the first place. The trick is using an extra table simply to generate the next product ID. You can add and delete a row from the table so it doesn't contain any actual data. Then use the number as the id in one of your two tables. You still won't be protected _by the database_ from entering the same number in each table, but as long as you never update those numbers you'll _probably_ be okay. The other solution is still better, however. – Larry Lustig Jan 26 '11 at 16:00

7 Answers7

9

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.

chaos
  • 122,029
  • 33
  • 303
  • 309
  • That's actually what we're trying to get away from because there are other levels of complexity introduced by that. One of the general fields is, for want of a better term, a version. Multiple versions of a product can be active in the system at the same time. – Endophage Jan 25 '11 at 17:13
  • That's not a complexity. This makes that much simpler. Are you trying to have all versions have the same PK? – Stephanie Page Jan 25 '11 at 17:15
  • 1
    I don't have enough information to know why that's a problem; all that means to me is that you have a unique key on `name, version` instead of just `name`. – chaos Jan 25 '11 at 17:16
  • We will have to use a unique id, version key but there are lots of places where those will have to be used as a foreign key pair which I am trying to avoid. I would rather have just one foreign key. It's something that can be done in PostgreSQL, I was just hoping MySQL had reached that point. I guess not. – Endophage Jan 25 '11 at 18:18
  • Okay, so the problem is that your primary key isn't a primary key because it doesn't uniquely identify the entities you're concerned with. The way out of your mess would be to rearchitect so you have a single unique ID for things. Trying to do this shared PK thing is pretty much just trying to compensate for bad database design by piling on worse database design. I can't say strongly enough TURN BACK NOW, but I doubt you will. 'Sokay, not like you'd be the first developer to save 100 units of pain now at the cost of 10000 units of pain later. – chaos Jan 25 '11 at 18:25
  • @chaos It probably makes a little more sense if I told you I'm working with a legacy system and trying to add and fix functionality. – Endophage Jan 25 '11 at 18:26
  • Yeah, it sounds like that kind of situation. :) All I can say is, if you can manage to work up a central product table where each row uniquely describes a product identity -- maybe with a secondary unique key on the legacy ID + version -- it will be a great help in getting the thing into sane shape. – chaos Jan 25 '11 at 18:29
  • @chaos We actually kind of have to go both ways. The version is a new feature. We had a top product table but not specialized tables for all products (some had specialized tables, some didn't). So fortunately our keys are already unique but we have to pull some columns that will be version dependent down into new product tables. I've just been firing off the odd idea on SO to get some input as I work through it. Having a shared key is something I've done in PostgreSQL and it worked really well so I was curious to know if there was a similar solution for MySQL. – Endophage Jan 25 '11 at 19:04
  • @chaos So maybe you can help me with this problem. I need to update the primary key of the specialized tables to not just be the product_id but the product_id and version (as you've noted already). However, I'm getting errors. It won't let me do an ALTER ... ADD PRIMARY KEY as it errors on there being multiple primary keys and it won't let me drop the existing key before adding a new one. Any ideas? – Endophage Jan 25 '11 at 19:26
  • Does it still error if you use a single compound alter statement? i.e. `ALTER foo DROP PRIMARY KEY, ADD PRIMARY KEY (id,version)`? – chaos Jan 25 '11 at 19:46
  • @Chaos: You don't need product_id in the subsidiary tables. Just re-use the original id value from products, making it the primary key value of all three tables. See my answer, below. – Larry Lustig Jan 26 '11 at 13:00
  • @Endophage: To remove an existing primary key you must first remove all foreign keys referencing it. – Larry Lustig Jan 26 '11 at 13:23
6

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.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • Yup. Just simple 1-to-1 relation. Only the 'main' table should have auto_incrementing PK. – Mchl Jan 25 '11 at 17:18
  • I don't think the design enfoces a single category... see my explanation below – Andrew Jan 25 '11 at 17:19
  • @Andrew: Actually, looks like _both_ a single category in the product name _and_ a table to allow multiple categories per product. In any event, the category issue is secondary to the layout of the product tables. – Larry Lustig Jan 25 '11 at 17:42
  • That's right. And it is called Supertype-Subtype in Relational terminology. The wiki amateurs have not heard about it yet. – PerformanceDBA Jan 26 '11 at 12:00
  • You answer is correct, but it is probably not complex enough. chaos and OP have gone for 2 **additional** `id` columns and their **additional** indices. Maybe they do not understand Foreign Keys. Gotta laugh, eh. – PerformanceDBA Jan 26 '11 at 12:11
1

It seems you are looking for table inheritance.

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

Then tables product1 and product2 would have all their specific attributes and a reference 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
Arnaud Le Blanc
  • 98,321
  • 23
  • 206
  • 194
1

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

But if you insist on another way...

There are two reasons for your shared PK. One uniqueness across the two tables and two to complete referential integrity.

I'm not sure exactly what "sequence" features the Auto_increment columns support. It seem like there is a system setting to define the increment by value, but nothing per column.

What I would do in Oracle is just share the same sequence between the two tables. Another technique would be to set a STEP value of 2 in the auto_increment and start one at 1 and the other at 2. Either way, you're generating unique values between them.

You could create a third table that has nothing but the PK Column. This column could also provide the Autonumbering if there's no way of creating a skipping autonumber within one server. Then on each of your data tables you'd add CRUD triggers. An insert into either data table would first initiate an insert into the pseudo index table (and return the ID for use in the local table). Likewise a delete from the local table would initiate a delete from the pseudo index table. Any children tables which need to point to a parent point to this pseudo index table.

Note this will need to be a per row trigger and will slow down crud on these tables. But tables like "product" tend NOT to have a very high rate of DML in the first place. Anyone who complains about the "performance impact" is not considering scale.

Please note, this is provided as a functioning alternative and not my recommendation as the best way

Stephanie Page
  • 3,875
  • 1
  • 18
  • 22
  • I could use a shared sequence in PostgreSQL as well but MySQL doesn't seem to have any equivalent. I'm also trying to avoid the ugly "have a spare table to store the next key value" solution. – Endophage Jan 25 '11 at 18:21
  • 1
    Well you can't avoid every suggestion... at some point you gotta dance with the one who brung ya. – Stephanie Page Jan 25 '11 at 18:35
  • And a shared sequence still doesn't buy you RI. You'd always need a single PK somewhere if you want solid RI. – Stephanie Page Jan 25 '11 at 18:36
0

You can't "share" a primary key.

Without knowing all the details, my best advice is to combine the tables into a single product table. Having optional fields that are populated for some products and not others is not necessarily a bad design.

Another option is to have a sort of inheritence model, where you have a single product table, and then two product "subtype" tables, which reference the main product table and have their own specialized set of fields. Querying this model is more painful than a single table IMHO, which is why I see it as the less-desirable option.

Phil Sandler
  • 27,544
  • 21
  • 86
  • 147
  • That really shouldn't be a downside of the three table model; I can make a view look exactly like the one table... voila! easy querying. I'm not really disagreeing, just sayin' – Stephanie Page Jan 25 '11 at 17:20
0

Your explanation is a little vague but, from my basic understanding I would be tempted to do this

The product table contains common fields

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

the product_extra1 table and the product_extra2 table contain different fields these tables habe a one to one relationship enforced between product.product_id and product_extra1.product_id etc. Enforce the one to one relationship by setting the product_id in the Foreign key tables (product_extra1, etc) to be unique using a unique constraint. you will need to decided on the business rules as to 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 (1 to many - many to 1) which would imply that each product can be related to many categories This can now stay the same.

Andrew
  • 5,215
  • 1
  • 23
  • 42
0

This is yet another case of gen-spec.

See previous discussion

Community
  • 1
  • 1
Walter Mitty
  • 18,205
  • 2
  • 28
  • 58