Designing table that inherits from different table

[alexjh]

I'm trying to design a site that will list data that comes from multiple sources. It's a bit of a PHP question but I think it's more of a mysql question.

 

I'd like to display a list of items that come from various tables that have different attributes. To use a car analogy, let's say that I want to display a list of performance parts, turbos, superchargers, and nitrous. Each of these things would have tables that describe them, ie turbo: boost, turbine rpm; supercharger: displacement, pulley size; nitrous: bottle size, pressure.

 

I don't want to put them all in the same table because that would cause a lot of trouble.

 

But I do want to display a summary page that lists them all in a mixed format, to do things like sort by price.

 

I was thinking of having a table that has 2 columns: id and type.

 

Is this a good way of doing things? Can I create queries that merge the data correctly, preserving common columns like price, description etc while keeping the different data?

 

Now if I'm going in the correct direction, how should I deal with the id. Should they be unique across all tables or should the 3 tables have ids that can be duplicated? Is there a way that I can auto increment a key taking into account the ids in multiple tables?

 

In PHP it's a simple switch statement depending on the type so I'm not worried here.

 

Thanks for any advice! I'm not stuck with any table format yet so any suggestions are appreciated!

 

Alex