I am looking for some advice on how best to approach creating a database with products that are available in different colours.
There are a few hundred products that need to be stored in a database, each of which is made up of a six numbered item code. For example 198403, the first four digits 1984 are for the item itself and then the last two digits, 03 in this case, reference the colour the item is. Items can be multiple colours; 198403 = Black, 198405 = Red.
I would like advice on how best to create this, do I create one database listing the first four digits (i.e the item itself) in one database and then reference the colours in another database or just have one database that lists each item and its colour?
If users search for an item just typing in the first four digits, then I want to be able to return back to them the item and show to them all the available colours. if however the user types in all six digits then only the item in the colour requested is initially displayed.
Hope that all makes enough sense for you to give me your thoughts.