Clothing database design - trying to get the foundations right.

[MaxP]

Hi peeps. I am new to this forum and have no experience in Access other than using some databases at work to enter data into.

I am working out the layout/plan for a database at the moment. I have a couple of questions:

My database revolves around clothes. Some of the data will be specific to only some of the items, some will be generic to many items. Later on, I will need to access other tables to give results based on the items entered. This is NOT for a shop btw, it is designed for use on items in someone's personal collection or something they are looking to buy.


Now, my understanding of database design is to break down the data you need to store in to related chunks and try and avoid repetition of data where possible. I.e clothes and shoes will have different sizing options, but can use the same colour options. So you would put (I am assuming) things like Sizes, colours into tables and link them as needed.


Okay now to my main question. I will need to be able to select an item of clothing as part of the database. To get to an item sometimes I will need 2 or 3 "levels" to get the item and correct style. E.g. Trousers/Jeans/Bootcut , others will be simple 2 level selections: Tops/T-Shirts . You will then enter other bits relevant to that selection (Size, colour, design details etc)

The way I am planning it is effectively a 2-part combo selection but I would then need Access to bring up the 3rd level if required and populate it with the right information (and then do the same to sizes or anything else that needs to be entered for the the item selected).

Can this be done easily or am I making this more complex than I need to? I have read elsewhere that using an intersection table to store the data may be a way to gobut in truth I have no idea what that means or how to even visualise it to grasp it.

If anyone can help give me a few pointers that would be great so I can go off and work on this and get more understanding into what I actually need to do.


AS I said at the moment I am just planning tables and will then work on relationships etc. But if I get the foundations wrong, I am doomed before I start.

Thanks
Max

[MaxP]

This is what I have come up with so far, which is probably not right as yet:



Because I do not know how to layout/design a couple of bits right, let me explain it a little. In the "ItemSize" table, you choose the main size (say "16") and an optional extra size (if available) such as "Petite". This a pair of jeans would be a Petite 16 as you would get in the shops. BUT if the item was a bra, you Access would pick that sizing and let you choose for example 36C or whatever.

Same applies on the ClothingItem box. You choose two of the options as demonstrated in the Table pointed to from "ItemType" and "ItemType2" and then if needed access would put a third option to locate the exact item. Ie: Trousers/Jeans/Bootcut - which then liked to Size would mean you have selected a pair of Petite 16, Bootcut Jeans.

This is how I am seeing the database in my mind and I just need pointers to make sure I am on the right track or already getting this wrong/over complicated.

If anyone can help I would be very greatfull.

Max

[RayMilhon]

Before you start designing your Database Do a search and read up on "Database Normalization" or more specifically "Database 3rd Normal form". this is the minimum you need to get it right. Make sure you understand this prior to settling on a design.

You have the right idea with your sizes and colours, but you might want to take it a step further. For example you stated that some items may have 2 levels some may have 3. make those levels tables. For example

ClothesType
clothestypeid Autonumber, Primary Key
Description Text --------Where the descriptions are tops or Pants or Skirts .....

ClothesStype
Clothesstyleid Autonumber, Primarykey
Clothestypeid Numeric, ForeignKey
Description Text

[RayMilhon]

You posted that description while I was typing let me look at it and I'll post any recommendations later.

[MaxP]

Thanks for this Ray. I will take the look at what you suggested and see if that can help me make more sense of what I need to do.

I had a vague understanding of breaking things down as you have seen and even what you have already posted helps me out. If you can make any suggestions on what I have done so far that would be very helpful. I am not looking for answers, only hints on what I should be looking at or where I am going wrong.

Thanks again.
Max

[MaxP]

Right. I have had a read on normalisation. I understand the points of 1NF and 2NF. 3NF confuses me but I think I just need to re-read it a few times when my head is a little clearer.

Tried to break down what I think is needed and have got this:

Items:
Item_ID (PK)
CategoryID (FK)
ClothingID (FK)
ItemSizeID (FK)
Colour
Pattern
Material
Item Specifics

ItemSize:
ItemSizeID (PK)
ItemSize1
ItemSize2

ClothingItems:
ClothingItemID (PK)
CategoryID(FK)
ItemType1
ItemType2
ItemStyle

ItemStyle:
ItemStyleID (PK)
ItemType2 (FK)
ItemStyle

Category:
CategoryID (PK)
Categories

Some of the items shown would have seperate tables to act as lookup tables in effect to get the possible options for populating the table.

Does that look like I am on the right lines as I think it is, but I am not even sure if it meets the 2NF Rules, let alone 3NF.

Any help is welcomed.
Max

[RayMilhon]

Here's something to consider just looking at the item size portion of your design

Table: ItemSize
ItemSizeid Autonumber Primary Key
Description Text

Sample Data:
1 Dress
2 Bra


Table: ItemSizeSubtype
Itemsizesubtypeid Autonumber PrimaryKey
Itemsizeid Numeric Foreign Key to Itemtype table
Size Varchar

Sample data
1 1 6
2 1 8
3 2 28

Table ItemType
Itemtypeid AutoNumber Primarykey
ItemsizeID Numeric ForeignKey
ItemtypeDesc Varchar

Sample data
1 1 None
2 1 Petite
3 2 AAA

Sample Query:
Select ItemSize.Description, ItemsizeSubtype.description, itemtype.Itemtypedesc
from Itemsize Inner join itemsubsize on itemsize.itemsizeid = itemsubsize.itemsizeid Inner Join
Itemtype on itemsizeid.itemsizeid = itemtype.itemsizeid Where Itemsizeid = 1

Results:
Itemsize Description Itemsizesubtype.description, itemtypedesc
dress 6 None
dress 6 Petite
dress 8 None
dress 8 Petite


You'll still need to do some research on how to build your actual data table to get the specific Item, Size and Style. I'm betting that once you figure this portion out you'll have a better grasp on how to build the rest of your DB.

[MaxP]

Thank you for this Ray, I will give it more thought today. I do have a further question which perhaps may help guide me a little on the design and understanding.

At the query stage I will be needing Access to look up some pre-defined data based on an item. Most of this will not be editable but be reference data which I set at the construction point. I am assuming that, with programming, Access can do a "if you see xxx, display yyy" or Jeans=1, Bootcut=3 so total to Display=4 sort of queries. Because it will be a case of comparing X to Y (and possibly to z, w & q) to give an answer, I have not included a data section in my main database table for this. Would that be the right approach as the query result would be subject to all of the data the user enters and the value/information potentially very different based on what the item is and the data seen.

I will try and sit down tonight after work and see what I can do based on what I know so far.

Thank you for the help so far.

Max

[orange]

Regarding your lookup question

At the query stage I will be needing Access to look up some pre-defined data based on an item......

The short answer is Yes. There is some info and example here http://www.trigonblue.com/AccessLookup.htm

[MaxP]

Here's something to consider just looking at the item size portion of your design

Table: ItemSize
ItemSizeid Autonumber Primary Key
Description Text

Sample Data:
1 Dress
2 Bra

[Rest Removed]

You'll still need to do some research on how to build your actual data table to get the specific Item, Size and Style. I'm betting that once you figure this portion out you'll have a better grasp on how to build the rest of your DB.

I have spent a bit of time drawing this out to try and grasp what you have done and see if I can follow the logic to it. I think I am following it but want to see if I am right.

The table "ItemSize" is a list of clothing items. They are stored in a list where (in the example) 1=A Dress, 2=A Bra and so on.

In the table "ItemSizeSubType" you say that : Item 1 in that list relates to Item 1 in the ItemSize table and equals 6, Item 2 relates to Item 1 in the ItemSize table and equals an 8, Item 3 relates to Item 2 in the ItemSize table and equals 28 and so on.

Similarly in the table ItemType you say that: Item 1 in that list, relates to Item 1 in the ItemSize table and has a blank value. Item 3 relates to Item 2 in the ItemSize table and holds the value "AAA"


So when if someone wanted to save an entry for a Size 8 Petite Dress, the database takes a look at the data and sees ItemSize we need is for a Dress so is Item 1, the ItemSizeSubType is looked at we know that only Item 2 relates to a dress and holds the value "8" - thus giving us a Size 8 Dress. With ItemType we know only item 2 relates to a dress and holds the value "Petite". Thus we now have a Size 8 Petite Dress.

So the database would in effect store it as "ItemSize=1", ItemSizeSubtype="2" and ItemType="2".


Have I grasped this right? I may have overcomplicated my explination but this is how I am understanding it. Can someone tell me if I am on the right track as if I am I *think* I may see how to do some of the other parts.

Thanks
Max

[RayMilhon]

Essentially yes. However one thing I should have made clearer is I tried to follow the nameing convention you used as far as I could. I used the appropriate table names just not where I should have.
The Itemsize table really is more of an item type. The size sub type should actually be item size and the itemtype should be the sub size. I tried to use your original structure adding to it as I went but I think I would have been smarter and made the changes in my earlier post.

Item Type would be what kind of item it is dress, pants, bra ....
Item Size would be the size 6,8,large,x-large
item subsize would be for those cases such as a bra which has a size such as 36 and a subsize such as C

The number values are used to relate and index the values to make your queries return the descriptions faster. The reason the subsize table is related to the item type data instead of the size data is that the subsize information is more directly related to the type of item than the size for example a bra can have a subsize of "C" all bra sizes 32,34,36 can also have a subsize of "C" and you always want to make a join 1 to many. If you have a many to many join as you would with size to subsize as each size bras can have any of the subsize and each subsize can be related to any size of bra but a bra will have 1 size and 1 subsize.

Hopefully someone or you can find somewhere that has a better explanation than mine.

[MaxP]

That actually makes a lot of sense. I was originally looking at it from how I would write it down or do it in excel. When I first drew what you wrote down into something I could understand it made little sense. But with a clearer head I started to see how the Foreign Keys came into place and that is something I could not see clearly before. I now see a pattern of how "x" and "y" give you the answer "z".

I can now start to see how this could be used with the item itself and perhaps other aspects which is what I needed to get. I also now start to see how this would normalise the data I am trying to store, by breaking into smaller, related pieces where it can be managed right.

I have a fair bit to work to think about now which is great. Thank you so much for your help Ray, that really has helped me a lot.

Max

[RayMilhon]

Your welcome. This site is my normal starting point when I have a question. You usually get an answer or at the very least a pointer to the answer.