April 30, 2008 at 11:15 am
Hi,
I am developing an application to a garment factory. I have a doubt in designing a table.
Basic tables:
Jobs, JobColors, Material, Units, Currencies ...
These tables are designed with normalization rules.
I got a problem at PurchaseOrderDetails
Main table is JobMaterial. It has materialid, jobid, supplierid, description and TypeFactor(which represents the type of order) means that the material is ordered based on size or colors or total qty.
1 for ByColor, 2 for BySize, 3 for ByQty, 4 for By Contrast colors
The main problem at the details of the sub table.
JobMaterialDetails
If typefactor is by size, i need to store the details based on size
ex: S - 2000pcs, M - 4000pcs, L - 4000pcs, XL - 2000pcs
So I will have 4 records per each size
If it is by color, White - 3000pcs, Portabella - 5000pcs, Black - 2000pcs.
If it is by general, Total qty 10000pcs
How can I design this table. If I take, ColorOrSize column, it will refer different values for diffrent typefactor. When by size, it will have Size and when by color, it will refer colorcode.
But colors are having referential integrity. So it is violated other than by color as typefactor.
What is the best way to design this table?
Can anybody suggest?
Thanks in advance
April 30, 2008 at 1:02 pm
Can you have a table with columns for each of the characteristics you need, and leave some of them null, and have a type column in that table, and some constraints as to which columns to fill for which types?
Usually, for this type of thing, I'd have different sub-tables for different types. If you can't do that, then a single table with some columns that aren't applicable in all cases, is the best option I can think of.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 30, 2008 at 1:10 pm
I would just make a "Color" column that is NULL unless TypeFactor=1 and a Size table that is NULL unless TypeFactor=2, etc. I definitely wouldn't recommend a ColorOrSize column.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2008 at 1:14 pm
a2zwd (4/30/2008)
Hi,I am developing an application to a garment factory. I have a doubt in designing a table.
Basic tables:
Jobs, JobColors, Material, Units, Currencies ...
These tables are designed with normalization rules.
I got a problem at PurchaseOrderDetails
Main table is JobMaterial. It has materialid, jobid, supplierid, description and TypeFactor(which represents the type of order) means that the material is ordered based on size or colors or total qty.
1 for ByColor, 2 for BySize, 3 for ByQty, 4 for By Contrast colors
The main problem at the details of the sub table.
JobMaterialDetails
If typefactor is by size, i need to store the details based on size
ex: S - 2000pcs, M - 4000pcs, L - 4000pcs, XL - 2000pcs
So I will have 4 records per each size
If it is by color, White - 3000pcs, Portabella - 5000pcs, Black - 2000pcs.
If it is by general, Total qty 10000pcs
How can I design this table. If I take, ColorOrSize column, it will refer different values for diffrent typefactor. When by size, it will have Size and when by color, it will refer colorcode.
But colors are having referential integrity. So it is violated other than by color as typefactor.
What is the best way to design this table?
Can anybody suggest?
Thanks in advance
I think you'd need to normalize that data a bit more. Instead of storing it as a single field, break it down by size and quantity and color. Based on not nearly enough information... I'd create a Job Detail to Color table and include the quantity there and I'd have another Job Detail to Size table and include another quantity there. The only quantity on the job detail table itself would be the total for the order.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2008 at 1:20 am
Hi ,
Thank you. I prefer your solution. I will have different tables for different typefactors.
I think it will be easy to maintain inventory also.
Thank you and all for sparing your valuable time.
May 1, 2008 at 7:41 am
Hi,
I have another constraint. Among all type factors, I can have ByColorAndSize.
Means, White -> S -> 500
White -> M -> 200
White -> L -> 700
White -> XL -> 1000
Portabells -> S -> 1000
etc...
Is it good way that having size and color columns in a table and having nulls if not by size/color/colorSize/contrast/general ???
Is this the good design?
thanks
May 1, 2008 at 7:53 am
Again, I'm answering this with little to no information, so please take anything here as mild suggestions, not major pronouncements...
What about a design something like this:
Table: Item
Columns: ItemId
Size
Color
Description
Cost
?
Table: Order
Columns: OrderId
Company
OrderDate
DownPayment
?
Table: OrderItems
ItemId
OrderId
Quantity
Now, you'll capture the basic information that describes and item and you'll capture the basic information that describes an order. Then you have a table with multiple rows per order that describes the items that make up that order. When you want to go back and group by size or by color, you just join and aggregate appropriately. Does that make sense to you?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 2, 2008 at 9:34 am
The two fashion systems I've written in the past worked like this:
Column Name
StyleID
ColourID
Size06
Size08
Size10
Size12
.
.
.
Size 24
Unsized
The meaning of the sizing columns is a property of the Style master, so Size06 to Size18 could mean XXS, XS, S, M, L, XL, XXL or 6, 8, 10, 12, 14, 16, 18. The content of the column is of course the number of units of that size.
So a work docket line looks like this:
[font="Courier New"]
DocketID StyleID ColourID Size06 Size08 Size10 Size12Size14 .....
20080502 BR63BKT001 020602010[/font]
Similarly an invoice line would have (at least) the following columns:
InvoiceID, StyleID, ColourID, ...sizing columns...
This allows you to easily aggregate by style/colour/size.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 2, 2008 at 9:46 am
Not arguing, just pointing out, Size06...Size24 is a pretty serious violation of 3NF. I'd think you could get the same results without that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 2, 2008 at 2:36 pm
Heh heh no worries Grant, you are of course absolutely right. In practice though, about 7 years of it, the table layout I've shown above was far easier to work with.
Having said that, performance was a big issue then, and returning a familiar style/colour/size matrix from a normalised table structure wouldn't present so much of a problem nowadays.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply