MySql | Link products to specific data?

[ne_plus_ultra]

I am a bit stuck -- and this is probably a simple task. However here is my dilema.

I am using MySQL Query browser and developing tables in my new schema. The products correspond as follows:

- Each product is only compatible with a specific year of engine.
- For example "Product A" is only available to you if your Engine Year is 2003, 2004 or 2005.

How do I set this up in a data table so I can include this in my dynamic pages? Do I create a separate table named 'Engine Year'? If so how do I link Product A to the corresponding years within the Engine Year table?

thanks.

[BrainDonor]

There's probably a number of ways to accomplish this. One way would be to create a crosslink table.

Products table would have the product_id and other product data.
Engine table would have engine_year and other engine data.
The crosslink table would bring them together by storing both the product_id and engine_year.

Then your select statement could look something like this:

select * from product, engine, crosslink
where product.product_id = crosslink.product_id
and engine.engine_year = crosslink.engine_year

It's just a thought...and I am sure others here will have their own way of doing this.

[ne_plus_ultra]

There's probably a number of ways to accomplish this. One way would be to create a crosslink table.

Products table would have the product_id and other product data.
Engine table would have engine_year and other engine data.
The crosslink table would bring them together by storing both the product_id and engine_year.

Then your select statement could look something like this:

select * from product, engine, crosslink
where product.product_id = crosslink.product_id
and engine.engine_year = crosslink.engine_year

It's just a thought...and I am sure others here will have their own way of doing this.


Okay - this may be a silly question - what is a crosslink table? Is this easily achieved in the MySQL query browser? Here is some more details on what I am trying to achieve. I have seen it done but not sure where to start....

The products correspond as follows:

- To be eligible for 'product a' the buyer must meet two qualifications:

1. Specify their 'model of their engine.'
2. Specify the 'year' of their engine.

Is there a way to structure my tables so that if a person specifies 'a specific year' and 'specific model' the correct product A, B, or C will pull up for them?

Do I create separate tables named 'Engine Model' and 'Engine Year' -- and then link to the product table with foreign keys? If so, how do I specify more than one grouping. For example product A is compatible with 2004-2007 engines and 10 different engine models.

I am new to mySql -- but a quick study -- hopefully this is easy to implement.

thanks.

[chazzy]

I'm not sure I've heard it called a "crosslink table" before, I think the most common name would be a cross reference table. It's essentially a table that has 2 mandatory columns, foreign keys to other tables. It's how you set up a many to many relationship between objects.

Let's say you have Engines A,B,C. And years 2000,2001,2002,2003. In 2000, only Engine A was available, then in 2001 Engine A and B were available, then in 2002 Engines A,B,C are available, then in 2004 only engines B and C were available. You would put entries in the cross reference table where Column A=2000 (year) and Column B = EngineA, then so on for each of these entries. You could then write queries to find all engines available in a year, all years an engine was available, etc.

So what you're doing is
Create a table YEAR_ENGINE_REFERENCE, with two columns that point to the Primary Keys on YEAR and ENGINE