Auto Incrementing Primary key field across multiple tables.

[Cynicide]

Hi everyone,

 

I\'m designing a database for a news/reviews site and I have created tables for all the article types in the site.

 

Features - News - Previews - Reviews - Rants

 

I want all of these to share a primary key field that auto increments. I just can\'t find any place that documents how to get this working.

 

Would I have to make a new table called Index and put the primary key field in that and make all the other tables reference that one?

 

Any help or pointers to examples would be great!

 

Thanks,

 

Cynicide

[gizmola]

Seems like you are asking for a combination of declarative referential integrity and triggers for a subtyped table. There is no magical way of handling this type of design, especially with mysql. Mysql doesn\'t have triggers.

 

I assume that there must be some substantial commonality in the set of columns between the tables that makes this type of code worthwhile, because you are guaranteed to need a case statement to determine which subtype table you will join to.

 

The typical way of handling this is with 2 main tables, and 1..x subtables.

 

Table1 (Your main table... you called it index, should have an auto_increment key called indexid.

 

Table2 (The subtype table)

This has a subtypeid key and description. The rows in the table will be: 1. Features 2.News 3.Previews 4.Reviews 5.Rants

 

Tables 3-7 in your case are the feature, news, preview etc. tables.

 

You set Table 2 to have a foreign key relationship to Table1. That is, there should be a subtypeid column in your index table.

 

In each of your subtype tables, you will include the primary key from the index table in them. Here\'s an example:

 

CREATE TABLE features (

indexid INT UNSIGNED NOT NULL,

othercolumns....,

FOREIGN KEY (indexid) REFERENCES index (indexid),

PRIMARY KEY (indexid));

 

When you do an insert, you will insert your row into index first, setting the correct subtypeid for the subtable... ie 1 for an entry into the feature table.

 

Immediately following the successful insert into the index table you:

 

$indexid = mysql_insert_id($dbh); (where $dbh was the handle used in the insert query)

 

Then have your case statement, and you examine the value of $subtypeid, which tells you which child table you should be doing your follow up insert.

 

You then do an insert into that table, with the appropriate columns, and using the $indexid to set the indexid column value.

 

When querying the results out later, you join index to subtypetable by the indexid column.

 

Ideally you surround this process with a transaction, so that if at any point an insert fails, you can rollback the entire thing. The availabilty and syntax for a transaction-like capability really depends on the version of mysql you are using.