Thread: Clothing database design
06-10-12, 04:01 #1Registered User
- Join Date
- Jun 2012
Clothing database design
Hi, I am new here and to database design so excuse me if what I write sounds confused or wrong.
I need to make some way of storing a list clothing items and accessories. I think a database over an excel document (for example) would be best as when I display the information on an item as I will need to reference another set of data at that time.
I am trying to design what I need at the moment and while in my head it is perfectly clear putting it down is proving very hard, and this has led me to a few questions.
My idea is to have clothing items as one set of data which then references others as needed to select the specific item. E.g you select "Trousers" it goes and sees what types you can have and gives a selection of "Jeans, formal, shorts etc", selecting jeans will bring a list of options on styles such as "bootcut, skinny, straight etc".
Some questions are generic like size, colour etc which would apply to all items
If "accessories" was selected and you ended up with a necklace, the database would need to ask about chain type, length, style, thickness, material - all of which can be from lookup tables.
The way I understand it, I would have a main set of data called "items" for example, in this would be a primary key for the item itself, the rest would be foreign keys relating to the look-up tables.
From this, does what I am saying make sense, and just as importantly have I got the basic ideas right. I don't want to put a lot of effort into this and find out I have gone off down the wrong path.
I have downloaded some free design software to create a plan and this is why I want to make sure of what I am doing. If I can rough out a plan correctly I can then look to make changes and build upon the basic design to what I actually need.
I hope that all makes sense and I am sorry if it is confusing. As I said what I need I can see clearly in my head but it is so hard for me to try and put it into words.
06-10-12, 04:56 #2Jaded Developer
- Join Date
- Nov 2004
- out on a limb
One of the fundamental problems with things like garments compared to more 'normal' products is that you can have what is ostensibly the same garment but in different colours and different sizes. if its a different fabric then it should be a different garment (but it may not depending on the company
I'd suggest you have a table for product types whihc has a self referencing foreign key, that should handle the hierarchy
garments |--Belts | |--Leather | |--Fabric | |--FakeLeather |--Trousers | |--Jeans | |--Chinos | |--Cord | |--Formal |--Socks | |--Ankle | |--Long | |--Leggings
say 'Boot Cut Jeans'
then have a table which defines sizes
a table that defiens colours
an intersection table which defines a specific garment type (ie the product, the colour, the size, and whatever else makes a specific instance of that product
a table that holds details of specific products, whether you do that as a transaction table (ie reflecting the stock movements
ferinstance you may have things that add to stock
returns from customer
things that subtract from stock
returns to supplier
then you have the tricky item of adjustments (which could be additive or subtractive usually as the result of a stock count)
waht I haev seen doen int he past is to do a regular stocktake, the run the result of any transactions after that stocktake
Last edited by healdem; 06-10-12 at 05:00.I'd rather be riding on the Tiger 800 or the Norton
06-10-12, 06:36 #3Registered User
- Join Date
- Jun 2012
Thanks for this. Had a look at what you suggested and what is in my head.
This database is not for a shop and if I was not clear on that I am sorry. The items will be personal items in a persons collection or which perhaps they are looking to buy. The database will then look at some other data (which the user would define separately) to give an idea of practicality.
I have drawn up a table for "Clothing Type" using the idea you said: Item / Type. The way I understand what you suggest is that if Trousers/Jeans was selected it would then call a list of types of jeans which is then selected and noted accordingly.
Given that most items will conform to the same sizing conditions - I.e dresses / Tops etc will be 8-20 for example - all socks are based on shoe size, then you would have a separate table for that which is then referenced as needed. Same applies to things like colour & material. Anything specific to a particular item, say a note about the design / pattern on it would be sorted in the main table as it would be specific to that one item and not necessarily the others.
So the main set of data would be something like this (the bits in brackets are examples of what I think would be stored in there / what type of table is referenced):
user_Item_id (Primary Key) Clothing Type (Trousers/Jeans) Clothing Sub Type (Bootcut) Size (16) Optional Size Detail (Tall, High Waist) Material (Denim) Colour (Black) Pattern (None) Item Specific Details (Has large belt loops for thick belts)
I have yet to work out what relates to what as such but if I can get the basic tables right I can start work on this and then built upon that.
One thought I have had, can one table be referenced by a number of tables. I.e could various items all reference the size table? I think it can in a many-to-one connection but I am not convinced I have understand this right.
06-11-12, 02:22 #4Registered User
- Join Date
- Jun 2012
Sorry for the duplicate post, I personally thought it was kinda different in terms of what I wanted to ask as it was more to do with implementing a method, not the working out what I actually need. Apparently I was wrong. So I have moved the post here in the hope someone may answer it for me and help me out a bit.
I am working out the layout/plan for a database at the moment. I have a question on this.
My understanding of database design is to break down the data in to related chunks and try and avoid repetition of data where possible.
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. 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
The way I am planning it is effectively a 2-part combo selection but would then need MS 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 that item). Can this be done easily or am I making this more complex than I need to? Someone mentioned using an intersection table to store the data but 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.
06-18-12, 19:03 #5Registered User
- Join Date
- Aug 2004
- Cary, NC
Just a couple thoughts and observations...
First of all, try to separate your thoughts on designing the logical data structure from your thoughts on how to ultimately design the lookup form. If the data is correct you'll be able to do what you want with it, including populating the combo boxes that you mentioned.
You can certainly separate your attribute data into their own tables, (size, material, color, pattern, etc). When dealing with the subtypes it really depends on your data. Take jeans for example, if you define the sub type table to include a link to the main type, you can then add everything related to a jean (skinny, bootcut, etc). But now you run into two design challenges. First, if you also have bootcut pants (not jeans), you would end up duplicating that attribute in the table, and secondly, you can only link one subtype to your main type.
So you create three tables: Type, SubType, and TypeSubType. Type includes the data such as Jeans, Pants, Shoes, Shirt, etc. SubType includes attributes such as Skinny, Bootcut, Heels, Flat, etc. The TypeSubType is the connection between the two and only contains the indexes from Type and SubType. You can now connect Jeans with multiple sub types and reuse those same subtypes for pants.
Take some time to think about the data and how things will be connected. It's a different situation if something is only connected once (as in size) or can be connected multiple times (as in subType -- straight leg boot cut?).
The center point of the database is your item table, and you can reasonably assume (for simplicity) that each item will come in one type, one color, one material, one size, and one pattern.
The design challenges are type/subtype and Size/subSize since they take two different yet related pieces of data.
Allowing type and SubType to be connected to item independently allows the possibility of choosing a subType that is not appropriate for the type (high heel jeans for instance). When the possibility exist for inconsistent data you really need to rethink the design. One possible path is to link the appropriate combinations of type/subtype in a join table and connect that table to items. This could also be a maintenance & logistical nightmare so think about how you want to define your items and how that information needs to be connected.