Help With Database Structure
Hello I am looking for help on how to structure my database.
I have many products, each has a unique ID or code...e.g. GH/254/JK. The start of the code (GH) corresponds to a customer. There are tens of customers some with 100's of lines of products. e.g GH/255/HL, GH/256/KL. Lots of the different products have the same contents but different packaging (GH/255/HL has same contents but different packaging to PP/255/HL).
I am making a database to display details for each product. SO at a simple level it goes something like this:
Product Code | Product Name | Description | Packaging
GG/255/PP | This is the name of the product | This is the description of contents | This is description of contents.
Originally I did a single table with all these products on which was working OK. I then decided I needed a way to reference the products against each other so I made a second table related to the first. The second table had a Product Code related to the first table's Product Code. I then made lots of columns for each customer so:
Product Code | GG | HI | PP...etc
GG/255/PP | GG/255/PP | None | PP/255/PP
This was to try and reference the products against each other. This broke down when I would then come across PP/255/PP and obviously it wasn't referenced BACK to GG/255/PP. I could constantly try and keep the 2 sets of products up to date with each other but this has already got out of hand and I often find that if I search for products with a GG code AND a PP code (in the PP field) I get ..say 500 products... but if I search for products with a PP code AND a GG code (in the GG field) I get say 492 products...so I have made a mistake somewhere in referencing.
I can't get my head round a way to keep all these codes referenced up against each other. The one thought I had was to make a products table for EACH customer...and then have a relationship from EACH customer on one table to every other table...Having 50 x 50 relationships = 2500. Way too many.
Has anyone got any other solutions?
I am very grateful for any help. If I have been too vague please ask for more information.