October 3, 2018 at 9:18 am
Guys,
I was just wondering if anyone has any opinions on how to model the following.
I have a business concept of Case_size and the current types are single, pack, carton and pallet. There may be others in the future like container or Full Load
Carton is the base size. for any given product there will be X singles in a Carton and Y packs. Therefore the number of singles in a pack is Carton Qty / Y.
For pallet and above the qty will be N cartons.
For future calculations, I will look up the Case Size and if it is less than base then I will divide the base qty by the case qty. If it is larger then I will multiply the base qty by the case qty.
E.g for Product ABC123.
single = 10 (10 singles to a carton), pack = 5 (5 packs to a carton) , carton = 10, pallet = 12 (cartons to a pallet); carton is base.
so if you order a
single you will get 1 (10 / 10)
pack you will get 2 (10 / 5 )
pallet you will get 120 (10 * 12)
I will need an attribute to identify the base case_size (isBase BINARY DEFAULT =0) with a check constraint that only one record can be TRUE
I will need an attribute to identify whether the case_size is a divisor or multipler
I could do this in one with a TINYINT where 0 = base, 1 = multiplier and -1 = divisor but it feels like this might lead to some scruffy code later on. If I have a second field is isMultiplier TINYINT I could have a standard calculation of
base.qty * (case.qty * ibase.sMultiplier) / (ABS( iase.isMultiplier - 1))Which feels clever but might be hard to understand.
Thoughts would be welcome
October 3, 2018 at 9:35 am
Of course I meant
(case.qty * base/isMultiplier * base.qty) + (ABS(base.isMultipliler-1) * base.qty / case.qty)
Either the multiplier or divisor will equate to 0 and the other will be a +ve value (asusming that the qty fields are not INTs, in which case you would need to CAST or CONVERT first
October 3, 2018 at 10:43 am
I don't follow.
So there can be multiple rows for Product ABC123. Why? To allow changes over time? Or for diff locations?
It might be helpful to do this logically, not physically. That is, do not worry about data types now, just specify the logical relationships.
Is Product ABC123 a unique row or not?
If not, it could make more sense to have a "base" table that is guaranteed to have only a single row for ABC123 that specifies the base carton size.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 4, 2018 at 6:05 am
The answer here might depend on the nature of your manufacturing process and ordering system. Some companies go so far as to record multiple SKUs that represent the same item, but in different packaged quantities. The pallet might well be the largest orderable SKU, while a six-pack might be the smallest orderable SKU. However, if this is not how your ordering system is set up, then you may have to continue to operate with having all these calculations. If the actual product were some kind of bottled beverage, counting individual bottles is at some point useful, but having separate SKUs (shelf-capable units (K because this came from Germany)) takes a lot of that almost entirely out of the ordering system, and leaves it largely to the manufacturing floor. Both options have pros and cons. The separate SKU deal takes the focus away from the individual bottle and places it more on the orderable unit, The alternatives place a focus on knowing more about the individual item than the packaging. It all depends on what the company needs to focus on.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2018 at 8:10 am
If there is only one product, I would make an auxilary table containing all possible/allowed number of these products and all packaging solutions. And have at least one row for each number of 'basic' units which are allowed. So first building the table from what is possible/allowed and then selecting from that.
Normally the different number of possible/allowed numbers is limited. You can order 1/2/3/4/5/6/7/8/9/10 etc. but for larger orders only specific amounts are usual, for example a number of 1200 is allowed, but the next number allowed is 1320 for example.
Offcourse if all numbers are allowed and there are a lot of different products, the table can become impractical.
Just my 2 cents and maybe not applicable to your situation.
Ben
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply