Solved I have a design choice to make and am not sure which is the best way to go. (1 Viewer)

MIkeD666

Registered User.
Local time
Today, 12:51
Joined
Jan 12, 2019
Messages
54
Hello every one, hope you are all nice and warm on this cold morning. I have built a database that is used of a retailer of clothing shop. They are a small one man business with one retail shop. with no buget at all.. My fee for doing this a pint when the lock down is over,


I need to add color options and size options (or choices) that the user can pick from.
i could make a table of sizes, like this.

table Name= sizes
record example
size id code= XXXX
Product id=XXXXX
SizeName= small ( example)

this would result is a table like this
SizeID Product ID SizeName
1 0012 small
2 0012 Meduim
3 0013 large
4 4560 36b
5 4560 36a
5 4560 32b
6 5694 28 insideleg


which then has to be linked the the main product table in a relationship. The are a few other ways I have thought of. but i would like to get peoples other option and other ways of doing it. with pros and cons it possible. The problem with is option is that the wold be a lot of duplication as below
side id size name product id
1 small 1234
2 medium 1234
3 large 1234
4 small 7891
5 medium 7891
6 large 7891
7 small 4561
8 Medium 4561


I don't want to pick a method and them find a better one a few days or weeks later.

Have a nice day

MIke
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:51
Joined
May 7, 2009
Messages
14,546
you need to add SizeID as well as ColorID to your "transaction" table.
btw, you have duplicst sizeID there(5).

create the colorTable:
colorID (autonumber)
color (short text)

on your form, add two comboboxes (for size and color).
sizeCombo is cascading, in that it will be "filtered" when the user
selects a product.
the rowsource:

select sizeID, [size name] from [sizes] where [product id] = [yourfomName]![product id];

on after update event of [product id], execute a Requery on the sizeCombo.

your colorCombo, does not depend on any thing, so simply the rowsource is:

select colorid, color from colorTable;
 

MIkeD666

Registered User.
Local time
Today, 12:51
Joined
Jan 12, 2019
Messages
54
you need to add SizeID as well as ColorID to your "transaction" table.
btw, you have duplicst sizeID there(5).

create the colorTable:
colorID (autonumber)
color (short text)

on your form, add two comboboxes (for size and color).
sizeCombo is cascading, in that it will be "filtered" when the user
selects a product.
the rowsource:

select sizeID, [size name] from [sizes] where [product id] = [yourfomName]![product id];

on after update event of [product id], execute a Requery on the sizeCombo.

your colorCombo, does not depend on any thing, so simply the rowsource is:

select colorid, color from colorTable;
Thank for your input it looks promising. I will give it a try and let you know
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:51
Joined
Feb 19, 2002
Messages
33,669
You need 1 table to define size options and a second table to define color options. You might also want a category table so you can have dresses, pants, jeans, tops, etc., and a Group table so you can group by men, women, boys, girls.
Then your inventory would be
ItemID
CategoryID
GroupID
ColorID
SizeID
Quantity

The first four data fields will be combos.

When catalog retailers do this, they use color coordination so sea green is the same color no matter where it appears in the catalog. For a storefront, you can probably just get by with simple color descriptions red, blue, green, navy, black, white, ivory, yellow, pink, orange.

If you're going to tie this into a POS (point of sale) system, you might want to get that first so you can see what it requires from your inventory.

Your inventory table can also include, purchase price and sale price or markup so you can easily keep track of the value of the inventory on hand. And if certain types of clothing are non-taxable, you can mark that either in the Category or the Group table depending on what makes sense. In the US, children's clothing is not taxable.
 
Last edited:

MIkeD666

Registered User.
Local time
Today, 12:51
Joined
Jan 12, 2019
Messages
54
Hello every one, hope you are all nice and warm on this cold morning. I have built a database that is used of a retailer of clothing shop. They are a small one man business with one retail shop. with no buget at all.. My fee for doing this a pint when the lock down is over,


I need to add color options and size options (or choices) that the user can pick from.
i could make a table of sizes, like this.

table Name= sizes
record example
size id code= XXXX
Product id=XXXXX
SizeName= small ( example)

this would result is a table like this
SizeID Product ID SizeName
1 0012 small
2 0012 Meduim
3 0013 large
4 4560 36b
5 4560 36a
5 4560 32b
6 5694 28 insideleg


which then has to be linked the the main product table in a relationship. The are a few other ways I have thought of. but i would like to get peoples other option and other ways of doing it. with pros and cons it possible. The problem with is option is that the wold be a lot of duplication as below
side id size name product id
1 small 1234
2 medium 1234
3 large 1234
4 small 7891
5 medium 7891
6 large 7891
7 small 4561
8 Medium 4561


I don't want to pick a method and them find a better one a few days or weeks later.

Have a nice day

MIke
thank you all for the input. will study each one and test them ,then let you know the out of each
 

MIkeD666

Registered User.
Local time
Today, 12:51
Joined
Jan 12, 2019
Messages
54
thank you all for the input. will study each one and test them ,then let you know the out of each
 

MIkeD666

Registered User.
Local time
Today, 12:51
Joined
Jan 12, 2019
Messages
54
You need 1 table to define size options and a second table to define color options. You might also want a category table so you can have dresses, pants, jeans, tops, etc., and a Group table so you can group by men, women, boys, girls.
Then your inventory would be
ItemID
CategoryID
GroupID
ColorID
SizeID
Quantity

The first four data fields will be combos.

When catalog retailers do this, they use color coordination so sea green is the same color no matter where it appears in the catalog. For a storefront, you can probably just get by with simple color descriptions red, blue, green, navy, black, white, ivory, yellow, pink, orange.

If you're going to tie this into a POS (point of sale) system, you might want to get that first so you can see what it requires from your inventory.

Your inventory table can also include, purchase price and sale price or markup so you can easily keep track of the value of the inventory on hand. And if certain types of clothing are non-taxable, you can mark that either in the Category or the Group table depending on what makes sense. In the US, children's clothing is not taxable.
thank you all for the input. will study each one and test them ,then let you know the out of each
You need 1 table to define size options and a second table to define color options. You might also want a category table so you can have dresses, pants, jeans, tops, etc., and a Group table so you can group by men, women, boys, girls.
Then your inventory would be
ItemID
CategoryID
GroupID
ColorID
SizeID
Quantity

The first four data fields will be combos.

When catalog retailers do this, they use color coordination so sea green is the same color no matter where it appears in the catalog. For a storefront, you can probably just get by with simple color descriptions red, blue, green, navy, black, white, ivory, yellow, pink, orange.

If you're going to tie this into a POS (point of sale) system, you might want to get that first so you can see what it requires from your inventory.

Your inventory table can also include, purchase price and sale price or markup so you can easily keep track of the value of the inventory on hand. And if certain types of clothing are non-taxable, you can mark that either in the Category or the Group table depending on what makes sense. In the US, children's clothing is not taxable.
thank you all for the input. will study each one and test them ,then let you know the out of each
 

MIkeD666

Registered User.
Local time
Today, 12:51
Joined
Jan 12, 2019
Messages
54
Thank for your input it looks promising. I will give it a try and let you know
thank you all for the input. will study each one and test them ,then let you know the out of each
 

Users who are viewing this thread

Top Bottom