I am working on a project which I will be programming in C# once the database design is complete. I need some help designing the database tables. I can't seem to rack my mind around how this can work. I have half the design complete, but I need some help and clarification how this can work.
Note, I am not using actual table names data, but this outlines the dilemma.
I have the STUDENT, PRODUCT, and COLOR data table. A STUDENT can have many PRODUCTS and many COLORS.
So far, I have a STUDENT TABLE with their NAMES.
A table for PRODUCT.
A table for COLOR.
The programming part is where this gets tricky. In the application, when a user logins, they come to the first screen. The first screen shows the student information. Here, they must choose a PRODUCT from the dropdownlist. (there could be 1-5 PRODUCTS)
Next screen, the user must choose the COLOR information that corresponds to the PRODUCT information.
PRODUCT_DESC_1 only corresponds to COLOR_DESC_1 and vice versa.
From your example, it is not clear what you actually have. There appear to be 2 products in each of the "desc"s. There are multiple colors in both.
How did there come to exist these "combinations" in one "record".
If i understand you will have people who can order products that may be of differing colors. So when the data is used it will be person/product/color multiple times in the result? If the person orders multiple of the same product with different colors, there would be additional rows returned?