Need suggestion for structure tabel.

[d_profesor]

Hello all,

I'm trying to database clothing process. From material purchasing to clothing sales. But now i'm in doubt how to create table structure for the Product. Each product will have six different size : S, M, L, XL, XLL, and CuS. if you have Product A, it will have these six size, say AS, AM, AL, AXL,..ACUS. So my question, how do i should store the Product A in tabel product? I mean, should i store it in six different Id in the table? or just only one id for it? Actually, I've tried to keep it in six id but then i got problem with creating form entry for Sewing Order, Finishing Order, etc..because just only for Product A, it will probably take too many record to entried (too many row if it on a continous form) So Ideally, I want for product A, it will take one row in entry form. I would like to have something like in the picture "iwanthis" rather than in the "idontwantthis" (file attachment)

for form entry... Could it be done if i store the Product A in six uniqe id? I don't have any idea how to do it.

Further, if i store the Product A in one uniqe ID..the problem is i'm also need to count product stock base on the product size. I don't have much experience in Access, So i really need suggestions for this..I'm afraid that if i make wrong way, it will bring big problem in the future..


Thank you

 

[goodguy]

First of all, why do you need product id on sizes? Can't you keep sizes in one table, products in another and use a junction table to track quantities for each product/size combination? Like this:
Table Products: ProductID, ProductName, and other related fields;
Table Sizes: SizeID, SizeName, and other related fields (contains records for all six sizes);
Table Batches: BatchID, BatchNo, DateStart, DateEnd, etc;
Table ProductSizes: ProductID, SizeID, BatchID, Qty, etc.

 

[d_profesor]

First of all, why do you need product id on sizes? Can't you keep sizes in one table, products in another and use a junction table to track quantities for each product/size combination? Like this:
Table Products: ProductID, ProductName, and other related fields;
Table Sizes: SizeID, SizeName, and other related fields (contains records for all six sizes);
Table Batches: BatchID, BatchNo, DateStart, DateEnd, etc;
Table ProductSizes: ProductID, SizeID, BatchID, Qty, etc.

Yes, I think that i've created something like you said, and that's mean when there is a transaction like sewing order for two product, A and B, and for each product, there's the order for size: S = 2, M=2,...Cus=2 . This mean that i have to type 12 record (row) on the sewing order form for only those two product? am i right? or i'm missing something here..Because for that transaction i've to pick ProductSizesId from table ProductSizes and there is six ProductSizesId for one ProductId, am i right? That's mean when there's an order for 4 ProductId (average product/order), I've to type 4X6=24 record or row just for one sewing order form. I feel that's not a good way. All i want is for one order with 4 product, i'm also type 4 record (row), So I created a form like in "ClothingDb" database that i posted in this reply (2003 format). There's six textbox for Qty that must fill for each size. But, the form imply that I don't need to RELATE tbl_Sizes with tbl_Product, or in fact I don't need tbl_Sizes anymore. Because Qty will entri base on size. So I'm about to delete this relation or delete tbl_Sizes and maybe i've to change other form that i've created too. But i'm doubt, is it normal for using? or there's another way?

I'm sory, maybe my explanation is very bad. My english is not good.

 

[goodguy]

I took a look at your database, and I am sorry to say that it is very messy. If there is one lesson a programmer needs to learn before starting programming, then it is database design. Without that skill, you are doomed to fail.

 

[d_profesor]

I took a look at your database, and I am sorry to say that it is very messy. If there is one lesson a programmer needs to learn before starting programming, then it is database design. Without that skill, you are doomed to fail.

it's ok. I'm sure you're right. i'm just starting to learn. can you tell me which part i need to fix it? of course i need to know what is your mean by the word "messy" . I learn Access just by doing, from this forum and of course. from google.

 

[goodguy]

Read up on good database design techniques, including normalization. That will give you the key understanding of organizing data into tables. There are many great resources online.

 

[John Big Booty]

Here's an article on Normalisation to get you started.

 

[d_profesor]

Read up on good database design techniques, including normalization. That will give you the key understanding of organizing data into tables. There are many great resources online.

Ok, then. Thank you very much for your suggestions. I hope you don't mind if i return back with question about normalization my db after i learn the normalization subject.

Here's an article on Normalisation to get you started.

Thank you for the link , I'll take a look of it. Hope you don't mind if i've question about this subject later.

 

[goodguy]

Feel free to shoot anytime, buddy.