I'm working on products database on Filemaker and I need help to decide the best way to do it.
for example, The same Tote style has 10 different colors. here the style means the same shape, price, material etc... everything is same except the colors. so it will be better to set up 1 tote style as a record in the product table OR counting tote blue, tote red, tote black as one record for the product table?
I thought in 2 cases of the option.
Option 1. If I count 1 tote (with 10 different colors) as one record, I can have 1 layout including costing, details etc on the same layout since all other information is the same except the colors.
Option 2. If I count tote-blue, tote-red, tote black as one record for the product table, I don't want to repeat all other same information so in this case, I might need to have cost breakdown table to set up the same information for the tote of 10 colors in one place.
each case how would I work on order table? how can I make the relationship between orders and product?
Pros- I can have less number of the product id and have the same information in one place.
Cons- another line item id to distinguish the same product in different colors.
question- Having one line items join table between order and product can be resolved for the relationship with order table and other?
|product id||color||Price||order quantity|
Pros- easier handling for the database sorting ( I might be wrong with this)
Cons- I need to repeat the same information(same price, same material, etc) for each product id.
question- Having a separate costing table can be resolved in this case? if then on coasting table how do I have to set up product id?
|product id||color||price||order quantity|
|tote 1 blue||blue||$10||5|
|tote 1 red||red||$10||2|
|tote 1 black||black||$10||3|
I would like to have an opinion to figure it our what is the best way to do for my case.