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