general data question 2

[DelphiAaron]

i have a database of spare parts, some of the parts might belong to more than 1 model of machine.
whats the best way to make the table so that i can fit more than 1 model to a part. ???

i thought that having multiple fields for the models (model1, model2, ect...) but if you only allow 5 fields and there were more than 5 models it's no good.

would i be better off to make the model field a memo type and then i can sql it (find model from partstable where modelfield contains "this model&quot would this work ??

maybee someone has a better solution. John Mutch Electronics

http://www.jme.com.au

 

[repete]

try three tables

machine table
****************************
machinecode machinename
001 my machine
002 that machine

sparts table
****************************
spartcode spartname
001 spare motor
002 4 inch bolt and washer

sparts usage table
****************************
machinecode spartcode quantity
001 001 1
001 002 4
002 002 8


Pete Bloomfield
Down Under

 

[DelphiAaron]

thanx for the advice, ill have a play with that idea..
John Mutch Electronics

http://www.jme.com.au

 

[DaPi]

John, hope you do more that "play" with this idea - it is THE WAY to deal with many-to-many relationships. DaPi - no silver bullet

 

[dannywild82]

daPi is correct. you need a sort of 'association class'

otherwise you will fail.

- dont bother with a quick fix. head in book all day breeds good results

 

[DelphiAaron]

whos john ??
what i cant get my head around is
1. why have a machine database all your doing is assigning a number to a number?
2. how do you asociate the multiple machines to the 1 spare part, if the part database has fields "part_no, description, location, cost, retail, to_suit_model" John Mutch Electronics

http://www.jme.com.au

 

[repete]

1. in the example, it just assigns a number, you can have any other information specific to tht machine, eg next maintenance date, purchase date etc
2. the "to_suit_model" field you have already is really the same as the spartsusage.machinecode field, but as that is not specific to the part (as there can be more than one machine that the part relates to) it better belongs in the third table rather than the sparts table.

3. setting up forms (screens) to maintain the data, and sql's to extract the data (for reports, display etc) is the next step after setting up your tables

let me know if you need more info

Pete Bloomfield
Down Under

 

[DaPi]

Sorry aaronjme - thought the name in your signature was yours.

I don't want to confuse, but I fear it has happened already . . . this is going to sound pedantic, but you will have no end of trouble if you don't think it through for your business:

In the previous postings there seems to be some confusion between the CONCEPT of the Machine (which perhaps we should call the "Model&quot and the INSTANCE of a Machine (e.g. the broken one sitting on my desk).

You should probably have TWO tables:
Model - may have attributes like model number, size, weight and THIS will be the table that is linked to the "spare parts CONCEPT" table by the "usage" table - this structure supports the idea that a Model XYZ has two 80Gb disks.
Machine - will be linked to the Model table (so that you can describe this machine conceptually) but this table will have attributes like serial number, purchase/maintenance date, broken/working status.

The same distinction MAY be needed for the spare parts:
If the spares are really sub-assemblies and (e.g.) carry a serial number that you need to track, you probably need both concept & instance.
If it is just a question of 1/8" bolts, you only need the concept table - which, in this case, could have a "number in stock" attribute.


If you are embarking on something that will become business critical, then I suggest you should buy some expert data-modeling advice. DaPi - no silver bullet

 

[DelphiAaron]

thanks for all your advice
i'll take all the information you've given me
an see how i go... John Mutch Electronics

http://www.jme.com.au