HELP!!!

[Kevin Rosenthal]

Greetings,

I am working on an inventory databse for a clothing company and I amhaving
fits trying to build my tables. Here is the data that I need to track. If
anyone can help with the basic table design, I can take it from there.

Style Number (combination of a fabric ID and a Body ID)
Color
Size (can have any number of sizes per style)

I also need to track the details of newly finished goods and of the items
shipped.

Here is what I had came up with but it seems I am missing something.

styles_table

StyleNumber
Color
size1
size2
size3
size4
size5
size6

(I have checked and no style has more then 6 sizes but some have only 1)

transaction_table

tansactionID (Either Invoice# for shipping or cutting ticket# for reciepts)
styleID
color
size1
size2
size3
size4
size5
size6

OK, Can anyone help me?!!?!?
PLEASE!!!!!!

I need to have a rough draft on this by Monday!!!

Thanks!!!

 

[tina]

anytime you have a table with data incorporated into field names (size1,
size2, etc), your tables are not normalized. beyond that, when you analyze
your tables, you see that in your styles_table, color does not describe a
style (it describes a specific product), and size does not describe a style
(it also describes a specific product). this is also an example of
non-normalized data. rather than writing a long post to explain the basics
of tables/relationships design, i strongly recommend you see links at the
following webpage to read up on data modeling:
http://www.ltcomputerdesigns.com/JCReferences.html
see "Database Design 101" and "Starting Out" links first.

refer to the following tables as examples to help you relate what you read
to your specific database:

tblFabrics
FabricID (primary key)
FabricName

tblBodies (whatever that is <g>)
BodyID (pk)
BodyName (or description, maybe)

tblStyles
StyleID (pk)
StyleNumber
(truthfully, i'm not quite sure how i'd handle this, based on the
information that the StyleNumber is a "combination of a fabric ID and a Body
ID". i'd have to know a lot more about the process and the data before i
made a judgement call. so just go with this setup for the purposes of the
example.)

tblColors
ColorID (pk)
ColorName

tblSizes
SizeID
SizeCode (like 8-10, 11, 6 1/2N, etc, etc, etc.)

tblInventory
InventoryID (pk)
StyleNumber (foreign key from tblStyles)
SizeID (fk from tblSizes)
ColorID (fk from tblColors)
CountOnHand (don't use the word "Count" by itself as a field name)
DateCounted (don't use the word "Date" by itself as a field name)

so if you have 20 items of style 112-53, in 3 sizes, in two colors per size,
you'll have 6 records in tblInventory, such as

InventoryID StyleNumber SizeID ColorID Count
1 1 1 1 4
2 1 1 2 1
3 1 2 1 2
4 1 2 4 5
5 1 3 3 7
6 1 3 4 1

hopefully the above example will line up correctly so it makes sense. the
CountOnHand and DateCounted will be entered in each record also; there just
wasn't room to include them. the above setup takes care of recording current
inventory, and illustrates the use of related tables.

after reading up on data modeling, you'll be in a better position to create
your rough draft of all your tables, including those needed for the other
tasks you mentioned in your post.

hth

 

[Kevin Rosenthal]

THANK YOU~!!

I will read the articles you mentioned and refine what you sent. But I do
want to thank you as your information has finally made the other info I had
read make more sense pertaining to my situation.

Oh, for the record, the "body" represents the actual style of garment. ie
Tank Top, Button Down Shirt, Skirt etc.... ;-)

Well, thank you again. I do appreciate it.
--
Kevin Rosenthal
Rose Valley Soaps - www.rosevalleysoaps.com

anytime you have a table with data incorporated into field names (size1,
size2, etc), your tables are not normalized. beyond that, when you analyze
your tables, you see that in your styles_table, color does not describe a
style (it describes a specific product), and size does not describe a style
(it also describes a specific product). this is also an example of
non-normalized data. rather than writing a long post to explain the basics
of tables/relationships design, i strongly recommend you see links at the
following webpage to read up on data modeling:
http://www.ltcomputerdesigns.com/JCReferences.html
see "Database Design 101" and "Starting Out" links first.

refer to the following tables as examples to help you relate what you read
to your specific database:

tblFabrics
FabricID (primary key)
FabricName

tblBodies (whatever that is <g>)
BodyID (pk)
BodyName (or description, maybe)

tblStyles
StyleID (pk)
StyleNumber
(truthfully, i'm not quite sure how i'd handle this, based on the
information that the StyleNumber is a "combination of a fabric ID and a Body
ID". i'd have to know a lot more about the process and the data before i
made a judgement call. so just go with this setup for the purposes of the
example.)

tblColors
ColorID (pk)
ColorName

tblSizes
SizeID
SizeCode (like 8-10, 11, 6 1/2N, etc, etc, etc.)

tblInventory
InventoryID (pk)
StyleNumber (foreign key from tblStyles)
SizeID (fk from tblSizes)
ColorID (fk from tblColors)
CountOnHand (don't use the word "Count" by itself as a field name)
DateCounted (don't use the word "Date" by itself as a field name)

so if you have 20 items of style 112-53, in 3 sizes, in two colors per size,
you'll have 6 records in tblInventory, such as

InventoryID StyleNumber SizeID ColorID Count
1 1 1 1 4
2 1 1 2 1
3 1 2 1 2
4 1 2 4 5
5 1 3 3 7
6 1 3 4 1

hopefully the above example will line up correctly so it makes sense. the
CountOnHand and DateCounted will be entered in each record also; there just
wasn't room to include them. the above setup takes care of recording current
inventory, and illustrates the use of related tables.

after reading up on data modeling, you'll be in a better position to create
your rough draft of all your tables, including those needed for the other
tasks you mentioned in your post.

hth

 

[tina]

comments inline.

THANK YOU~!!

you're so welcome! when i can help somebody "turn on that lightbulb", it
really makes my day.

I will read the articles you mentioned and refine what you sent. But I do
want to thank you as your information has finally made the other info I had
read make more sense pertaining to my situation.

data normalization and relationships are difficult concepts, especially when
you have to get it all by reading. i think a face-to-face instructor-student
interaction is much more effective (not to mention faster). though you
really need to learn this *first* in the developer learning curve,
unfortunately most basic Access classes either skip the subject entirely or
barely touch on it. i had to take a separate data modeling class to grasp
the concepts firmly, and of course i'm still learning some ins-and-outs (in
Access, the learning never stops). so if you've been reading up on it
already, good going, and if you get a handle on the subject entirely from
"paper", you deserve two thumbs up!

Oh, for the record, the "body" represents the actual style of garment. ie
Tank Top, Button Down Shirt, Skirt etc.... ;-)

LOL, what a relief - i wondered if we were talking about a clothing company

Well, thank you again. I do appreciate it.

good luck with your db. once you work out a normalized table structure, you
might want to post it in a new thread and ask for a critique. there are lots
of skilled folks here in the newsgroups to advise you on that, or with any
other questions or problems that pop up.

 

[geomike]

When I did this last year for a small manufacturer, I had to consider the
same issues. Eventually I had to create a unique ID for each of the model
and styles/sizes. To make it simple for office staff to understand and
management to audit, we selected something that looked like style-color-size.
ie 115-brown-XXL
A bit cumbersome, but the user wanted something they could easily recognize.
Maybe this will help you too.