I can understand what you mean there but a one to many relationship is as my example in database terms. Adding the one to many in the relationships box helps preserve things like referential integrity etc
The compatability tables only needs to hold consumable IDs and printer IDs so just 2 data fields.
The printer table could hold the location table as well as you cant have one physical printer in more than one location.
We have more than one model of the same printer onsite and so it can be in many locations.
If then you did the printer key by serial number then add a single location field to that. It would make making queries and reports so much easier in the long run.
Originally Posted by edd
I'd still be inclined to have the compatibility records as
rather than lumping all the values into one field (and having separate printertype and printerinstance type tables as Jamo has).
Otherwise I think you'll find you're looking for a way to select just one item from a comma-separated list of cartridge types! Also, to be honest, it'll work a lot better with the allocation side of things.
Just how I'd do it anyway!
then you will need a printer_type table between printer and printer_consumable with another one-many.
the printer will store the name and location of the printer
the printer type will store which carts are compatible
A printer can only be of one type and a printer type applies to many printers.
The information being held in the Locations Table will now be stored in the Printers Table. Also the Consumables Tables will now have one consumable to the printer, like Jamo and Matt has mentioned above, as I got the combo box to only show the compatiable cartridges to the selected printers.
Thanks guys for your help.