Compatibility Sequence in MySQL

[Seriform]

Hi

I am setting up a site for a printer retail outlet and am having difficulty working out the best method of setting up the MySQL database. The customer wants visitors to her site to be able to search for products by the printer make, printer type or by the correct cartridge name/code. Each printer has multiple cartridges that are compatible with it including some which are compatible with multiple printers. I am trying to avoid data replication and keep things within the database as small as possible. Some printers have up to 70+ compatible cartridges. I am wondering what is the best method of storing the compatability of each printer &/or cartridge/s but am not sure of the best method:

I need to be able to use the join statement to display the results and am not sure which mysql field type would be best for storing a sequence i.e. printer A is compatible 5,8,9, 15,23,24 while still being able use join and making a site search engine in a dynamic form:

>Epson Printer (list/menu)
>Printer Models (list/menu)
=Results

>Enter Printer / Cartridge Code in a Search Bar
=Results

Please can someone help - I am quite new to MySQL and this problem has been buggin' me for a weeks!


Cheers,

Seriform

 

[sleipnir214]

Why in the world would you store a sequence of compatible cartridges? It's wasteful of space, difficult to search and update, and makes nearly no use of indeces to speed queries. MySQL is a relational database. That means you can relate the records in one table to the records in another.

I strongly recommend that you use three tables to store your printers, cartridges, and compatibility.

The first table stores printers. Each rows in this table will contain an auto_increment id and data on a make and model of printer.

The second table stores cartridges. Each row of this table will contain and auto_increment column and columns describing a make and model of cartridge.

The third table will record the compatibility of cartridges to printers. Each row in this table will have two entries -- the row id of a printer and the row id of a cartridge.

In your select queries, you will relate the three tables using MySQL's JOIN clause.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Seriform]

Thanks - when put like that it is v. simple - doh!

Seriform