Thread: Table Design Issues
07-27-16, 07:03 #1Registered User
- Join Date
- Jul 2016
Unanswered: Table Design Issues
I'm looking at designing a database for a plastic moulding company, but have very little database experience and have an issue with the design of my tables.
Designing the tables for material seems to be the area that I'm struggling with the most.
There is 3 types of material that is used:
Virgin Material - this is material that we buy in, often uncoloured and found in pellet form.
Masterbatch - this is a strongly coloured and found in pellet form, this is added to the virgin material at 1% - 8% to change its colour.
Regrind - this is material that has been previously moulded but is ground back into pellets to be reused, this is usually added at 20% - 50%, or it can even be moulded on its own (100%).
Mixes can be as follows (V = Virgin material, M = Masterbatch, R = Regrind):
However, this is complicated further by the fact that 2 or more different virgin materials can be in the same mix, or 2 or more different masterbatches, or two or more different regrinds.
So in theory you could have:
... or any other combination, the only rule is that Masterbatch can never be alone ( you will never have just M or MM)
I originally thought of designing a table for mixes as follows
Virgin material 1 ID
Virgin material 2 ID
Regind 1 ID
Regrind 1 percentage
Regrind 2 ID
Regrind 2 percentage
Masterbatch 1 ID
Masterbatch 1 percentage
Masterbatch 2 ID
Masterbatch 2 percentage
But as you can see this is fairly impractical, you would need an infinite number of fields to allow for mixes with many different types of different materials in, and would also end up with a lot of null fields.
Not sure how to get around this problem, and it is only made more complicated by the need to track batch numbers which are assigned to all virgin materials and masterbatches.
Any help or advice would be greatly appreciated
07-27-16, 07:32 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
sounds like you need to push the actual composition of a mix into a sub table
RawMaterials 'defines specific materials (eg re grind, virgin, masterbatch, etc
Mixes 'stores the common data for a mix (eg dates (date created, valid for), description, customer?
MixID 'fk to batches
MaterialID 'fk to raw materials
PercentComposition 'identifies how much of this material is required for material sepcification
need to implement a trigger or rule that ensures a batch percentage comes to 1 (or 100) depends on how your store the percent composition
alternatively you could define say an upper & lower limit (and say 'ideal') to allow for creating a mix defintion
then when it comes to production you actually create a batch from the mixes & mixSpecifciations but instead of upper and lower (and ideal) yuou just store the percent of each material used
the problem you are going to hit is colour. should each colourway of each polymer be a separate material and therefore require a separate mix definition, or shoudl it be a generic eg colouredX material, and then defien the colours used when you create a production batch. I guess that comes down to whether you woudl use the same percentage composition rules for every colour.
if say you had to use more of masterbatch1 to get a, say a dark red, as opposed to a pale red, or if say you had to use two masterbacth for one colour but only 1 for others then you probably need to defien a spearate mix for each and every colour of each and every mix definitionI'd rather be riding on the Tiger 800 or the Norton
07-27-16, 08:37 #3Registered User
- Join Date
- Jul 2016
Sorry, I'm really not very experienced in this field.
So I would create the following table?
Description (Virgin, Masterbatch, Regrind)
Where would I put the name, grade and supplier of the material?
Saying that, for regrind, we would be the supplier, as we regrind our old mouldings to produce these pellets.
Then I become lost I'm afraid, I don't understand the mix specification table.
We have virgin material delivered to to us on 1 tonne pallets, this is then distributed to the different machines in the factory using this material, or the mixing station. Often masterbatch is fed in using an automatic feeder that constantly adds a set percentage to the virgin material, or virgin material/regrind mix (that has come from the mixing station) when it is sucked into the machine. If the machine does not have a feeder, the masterbatch is mixed in at the mixing station too.
Sometimes the same job is running in a machine for months, and uses several pallets of material with different batch numbers.
Is it possible to say that all machines running on a certain material, and material mixed that includes this certain material between the time a pallet of that material has been delivered and the time that is has been all used, have used material of a certain batch number and then this batch number is added to a list next to the record that was created when job first started in production. So each time a new pallet of material is delivered, its batch number is put in a table and every job running that is using that material has the batch number added to its record?
From this we could create a certificate of conformity so when delivering parts to our customers, we will know which batch numbers have been used to create those parts
You are correct to say that mixes can be identical other that the addition of more or less masterbatch, and I understand that if one job used the same virgin material but 2% of masterbatch, instead of 1%, a new mix would need to be created.
Sorry for being a pain, and not really grasping what your trying to explain to me, but I hope this additional information helps.
07-28-16, 13:29 #4Registered User
- Join Date
- Jun 2015
- Boston, MA
He's saying you need to break this into a few tables.
One table describes raw materials (ID, name, grade, supplier). Another table describes the mix (ID, name, color, ratios (100:10:5 perhaps) material 1 ID (from raw-materials table), material 2 ID, etc.). A third table could be for raw material lots (ID, raw material ID (from raw materials table), date received, expiration date, etc.). A fourth table for mix lots (Mix-Lot ID, Mix-Lot date, mix ID (from mix table), material-1 lot#, material-2 lot#, etc.).
It's important to have a unique identifier for each row in each table (the "primary key") and use that ID in other tables (a "foreign key) to reference the data in that row.
07-28-16, 15:40 #5Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
Id look at something like this. this isn't finished, this isn't 'perfect', its an approach to the problem
customers contains details of your customers (eg name, contact details and so on)
I'd probably use an alphanumeric code instead of an autoincrement number for this (eg ASTONE01,SMITH35) as its easier to read for humans. just beacuse its easy to use an autonumber column doenst' mean you should always use it. sometimes there are obvious pre existing 'natural' key. an autonumber mearelyt is an easy way of gettign a guaranteed unique number in a system. I miught be temtped to use a simialr approach in for the raw materiasls,in stead of using an autonumber use say ABS_RRGGBB if you used an RGB pallette, or NYL_CCMMYYKK if you used CMYK or perhaps even pantone.
mixes contians details of what this 'recipe' is so it may include a descripition and other relevant stuff (eg customer Astones green ABS, the customers reference and so on, it coudl also include any special requirmements (eg customer astones is a picky beggar and tolerances are +/- .1% (equally that could be inbuilt intot he mix details as an upper /lower proportion or tolerance...
material types defines your material codes (eg virgin, masterbatch, regrind etc....)
suppliers contains details of, guess what your suppliers
raw materials contains details of specific materials EG ABS Dark Green, ABS NAvy Blue etc....
rm_details provides a means of accommodating buying the same material from different suppliers (IE you could buy your Nylon from more than one supplier so this provides a way of saying I don't care where the material comes from, it can be supplier A or XX....
mix details contains the recipie definition where you define how thi specific mix of astones ABS dark green is made. you could extend that to allow for some variation. ferinstance a customer might allow you to use more regrind f vary the precise recipe. that needs more thought is that is required. but you need to implement arule somewhere that makes certain that the sum of the proportion across all the materials used in this mix is precisely 1.
prod batch contains detaisl of an actual order of this recipe (mix) ie its 50 Kg of Black Nylon. working back through the data model you could then
find out who its for (because we know the mix type, that knows the customer ID, which in turn means we know the customer
through the mi details we know what different materials are required, because we know the proportion of each from our required batch size of 50Kg we know thats made of X% of this, % of that...... and we can convert that %ge into an actual amount by multiplying that with the batch size
this is not meant to be a fully fleshed out model, its meant to illustrate ideas, cncepts and areas you need to think about. your eventual model will reflect the decisions you need to make that reflect your requirements. ferinstance you may say yopu only use one supplier for anyone product so there is no need for the complication of multiple suppliers for the same item.
bear in mind a supplier or customer could be generic. for a sup0plier black nylon is ubitiquous it doesn't matter where it comes from, similarly you may say that some customers are not big enough or choosy enough to demand their own specification so they use a generic formula for ABS.
you coudl end up with multiple formualtions for the same Green ABS for different customers, or equally the same basic formula for customer X but each one uses a different masterbatch to give different colours. in many ways this is not to dissimilar from the problems the clothing industry has (they can have what is essentially the same printed design, in different sizes, different colourways and so on.I'd rather be riding on the Tiger 800 or the Norton