Database Design

[JimJam]

Hi,

I am trying to design a database to hold all of our ink cartridges we sell
in and also which printers they go in. I have created a table for the ink
cartridges, which has a Lookup value for what type of cartridge it is (Black,
Colour, Cyan etc etc).

When I then create the printer, I want to have a field called 'Black OEM
Cartridge' and only then present me with the cartridges that I created and
set to Black in the cartridge table.

I am totally new to Access and I got a few books, but none go into this kind
of detail.

Please help

Cheers

J

 

[TonyT]

Hi JimJam,

I think you will be best off with 4 tables;

tblPrinter
PrinterID
PrinterName
PrinterType
other Printer related Info

tblColo(u)r (sorry English user here )
ColourID
ColourDesc <- colour here

tblCartridge
CartridgeID
CartridgePartNumber
CartridgeName
CartridgeType
Other Cartridge info

tblFitment
PrinterID
ColourID
CartridgeID

This 4th table resolves the many-to-many relationship you have between
printers and cartrdiges, ie. one cartridge will fit many printers, and one
printer can have many cartrdges. You will end up with one line in tblFitment
for each cartridge for each colour for each printer, so the same cartridge
will appear many times, but each record will still be unique as it will be
listed with a different printer.

With this setup, you can select the Printer Name from a combobox in your
form, and have a second combobox or listbox display all the cartridges
available for just that printer to select from.

Also it would be easy to see which printer one cartridge fits by searching
on PrinterID in the tblFitment table.

hope this helps,

TonyT..

 

[Bruce Meneghin]

I can't see a need to separate out the colors. The color(s) of a cartridge
is one of its attributes.
Depending on your needs, you may want to give consideration to the fact that
some printers allow alternative cartridge combinations: high capactiy black
OR low capacity black; 1 black + 1 3-color OR 1 large capacity black.

 

[JimJam]

Hi Tony,

I think I can see where you are coming from. Is there any chance we could
chat on msn or something (if you have the time to help me that is?). I have
been pulling my hair out for the last few days trying to get my head round
this.

Please let me know.

Cheers

J

P.S. I am English too, so colour is fine with me lol

 

[TonyT]

Hi JimJam,

should be no problem, provided we can arrange a convenient time - evenings
are best for me, or possibly friday. Can do msn or prefer IRC whichever you
like.

TonyT..