Normalising tables to illustrate printer compatibility with which cartridges

[j.smith1981]

I am having some problems getting my head around hows the best way to do this.

 

Also any tips on indexing etc (going to be querying this allot I think, I was under the impression anyways that indexing a searchable field is always the best method to go by, is that correct?).

 

Anyways, you know one printer has many cartridges available to it?

 

Likewise many cartridges are compatible with many printers, many to many relationship right?

 

I know I need to create a form of a junction table in the middle of printers and cartridges tables but.

 

What would be best joining them with? Their own generated ID's (good old auto_increment, if so how would I go about getting these working?).

 

Is there any other? I mean I was under the impression that I could use the printer number/name and then go for the cartridge code and then go off that, would this work?

 

I was just wanting to do this properly as its the biggest project I have ever done!

 

Thanks and I look forward to any suggestions,

Jez

 

I think its mainly about converting theory I have learnt from a database, but still questioning my knowledge lol, which is very good, just I cant seem to understand what to do here lol, either of the 2 and then the indexing advice would help.

 

 

[JonnoTheDev]

printer

========

printer_id (MI Primary)

printer_name (VC)

 

cartridge

========

cartridge_id (MI Primary)

cartridge_name (VC)

 

printer_to_cartridge

========

id (INT AI Primary)

printer_id (MI Index)

cartridge_id (MI Index)

 

OK you can have 1 printer to many cartridges i.e

 

printer

========

1, HP Deskjet

2, HP Laserjet

 

cartridge

========

1, HP 301 Ink Black

2, HP 348 Inkjet Photo

3, 35A Black Toner

 

printer_to_cartridge

========

1, 1, 1

2, 1, 2

3, 2, 3

 

The above relates a HP Deskjet printer to both HP 301 Ink Black & HP 348 Inkjet Photo cartridges. The HP Laserjet can use the 35A Black Toner.

 

A simple query to get the data would be:

SELECT p.printer_id, p.printer_name, c.cartridge_id, c.cartridge_name FROM printer p INNER JOIN printer_to_cartridge ptc ON(p.printer_id=ptc.printer_id) INNER JOIN cartridge c ON(c.cartridge_id=ptc.cartridge_id) ORDER BY p.printer_name ASC