Hi pals, lately I've been too depressed to think straight. I'm struggling with a basic database design in access which is quite honestly embarrassing...
Anyway, I have tractors and diesel engines in their own tables with their own connection via form between them. As a rule of thumb I dont make relations via tables because it gets too messy and harder to implement changes later.
Now, there are spare parts. Some apply to one or many engines and thus in turn might apply to one or more tractors. Others apply only to tractor models because are things like say, a seat, the tank fuel, a headlight or other stuff that don't have to do anything at all with the engine whatsoever.
The original spare parts have their own codes wich sometimes can be more than one unique original codes that represent the very same piece and application. This happens because the tractors companies seem to get fucking broke every so often for some darn reason and they get bought, fusioned and sold several times in the course of their lives and generally each time this happens the companies that get in charge apply their own nomenclature to the old parts.... AND the issue is, the spare parts manufacturers sometimes keep up, some other times they don't and can be using any one, a few or all the codes in their catalogues, which is the final part of this messy issue. The other brands (not original) spare parts.
Thankfully those only ever employ a single code. So my mind is bending trying to find a way to make a link between:
not original single spare code -> one or many original codes that represent the same spare part -> one or many tractor models AND/OR engines.
In a way were I establish the original codes equivalence only once and then every time I assign a single one of them to a spare part all the others are assigned as well to it.
An example would be the following:
A manufacturer called Sparex has a pump with the code 542434
This pump OEM's are AL10681 AL16301 AL37753.
This pump is for the brand John Deere tractors models 500, 510, 700 and 710
So how to do so that next time I add a pump from say, ACME & CO that is the same as Sparex 542434, the user selects a single OEM and the other two are included too automatically.
I feel like the solution is in front of me but cant grasp it darn it.
Ok, I was told the premise was not clear, so sorry if Im not being clear.
The program is MS Access 2007.
The data base is for a spare parts inventory.
There are spare parts wich have a brand code like say ACME brand, code ACME123. Which is a piston. Spare parts factories only have a single code for each product.
Then there are OEM that are the codes applied by the tractor manufactorers themselves. This codes can be one or many for some reasons. In this example, the ACME123 piston could be for John Deere engines A, B and D. And have John Deere OEM's JD123, JD1BC, and EJDABC. This three OEM's all refer to the very same piston.
Ok ACME has in its catalog the piston like this: ACME123 (OEM JD123)
Sparex has the same piston as spx5512 (OEM JD1BC, EJDABC).
What Im trying to find the best way to merge the 3 OEM as one so if the user in the field OEM inputs any of the 3, the 3 are selected as the OEM for that spare part.
So far all I have thought is brute forcing it in a table were each code has a row for each equivalence. Which mean in the example the codes would ocupy 6 rows of this table. But this is exponential, no, linear. A piece with 12 OEM (which sometimes they have) would ocuppy 144 rows for all the possible cross references.
Author
Topic: help designing a data base (Read 2987 times)