Shop database and normalization help
Hi,
I am attempting to understand how onlines shops for example a t-shirt shop create their tables to be fully normalized. I fully understand normalization to 3NF (or at least I think I do lol) and I am not looking to normalize beyond 3NF.
What I do not understand is how to store a situation such as this in a database.
T-shirt Table
-------------
tshirt_id
type
size
the t-shirts could be in up to 5 different sizes so when I split this table to normalize it I get somthing like this:
tshirt_id | tshirt_size
---1----------1
---1----------2
---1----------3
---2----------5The problem is that there is still redundant/replicated data! because the tshirt can have many different sizes. The other ways i can see this being done is:
tshirt_id | Size1 | Size2 | Size3 | Size4 | Size5
----1--------N--------Y-------N--------Y------Y
----2--------N--------N-------Y--------Y------N
----3--------Y--------Y-------Y--------N------Y
----4--------N--------N-------N--------Y------Y
----5--------Y--------Y-------N--------N------NSurely this is not the most effective way of doing this.
Any help would be greatly appreciated.
Relentless.
pr0gr4mm3r posted this at 18:44 — 3rd November 2008.
He has: 1,502 posts
Joined: Sep 2006
To be fully normalized, you need three tables:
Sizes
size_id | symbol | description
1 | S | Small
2 | M | Medium
(and so on)
Tshirts
tshirt_id | (other tshirt fields)
Tshirts_Sizes
size_id | tshirt_id
This sets up a many-to-many relationship. There are multiple sizes that belog to multiple tshirts. List all the available shirts in one table, all available sizes in the other table, and connect the shirts to the sizes by using their IDs in the third table.
S33ker posted this at 10:47 — 4th November 2008.
They have: 38 posts
Joined: Jun 2008
Thank you for your reply
I understand that and have gone through that process. But what if size tshirt with id 1 is available in 3 different sizes:
Tshirt_Sizessize_id tshirt_id
1 1
2 1
3 1
Is this not redundant data?
Also I have been thinking about identifying each tshirt, each different tshirt whether it is the same make or what ever is still going to need a different ID even if just the size changes. But then you will have millions of records the same apart from one field. is that not redundant data too? for example:
tshirt_id tshirt_style tshirt_color tshirt_size1 casual black M
2 casual black S
3 casual black L
4 casual blue S
5 casual blue M
6 casual grey M
7 casual orange M
8 casual green M
9 casual purple M
10 casual red M
Is there no better way?
Thank you so much again and im sorry if I am going completely of the rails.
Thanks,
Relentless.
pr0gr4mm3r posted this at 13:54 — 4th November 2008.
He has: 1,502 posts
Joined: Sep 2006
Nope, that is correct.
So it looks like you have other attributes other than size. OSCommerce has a setup that allows attributes in a separate table, so it may be beneficial to study their database design. Instead of having a table for sizes, you can have a table for attributes in general.
S33ker posted this at 22:50 — 4th November 2008.
They have: 38 posts
Joined: Jun 2008
Great thank you so much.
I will take a good look at OSCommerce.
Thanks,
Relentless.
Zaccaria posted this at 16:34 — 24th November 2008.
They have: 32 posts
Joined: Nov 2008
I will take a good look at OSCommerce.
Thanks,
Relentless.
OSCommerce should be able to make this easier.
FireFleur posted this at 03:20 — 27th November 2008.
They have: 11 posts
Joined: Oct 2008
If you think about it each T-Shirt of a different size, colour and design is a separate stock item.
You will end up holding stock on each of those criteria, the idea is to model to unique item, then create collections.
So say you have a TShirt called the TBird, that TBird is a collection, you actual stock items would be:
TBird Red XL - 4 in stock - price
TBird Red Sm - 6 in stock - price
TBird Blue XL - 7 in stock - price
... etc
That's the way to think about it. You can share price but I wouldn't bother, keep the flexibility maybe blue dye will be more expensive, or less material is used in a small etc.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.