Bay 12 Games Forum

Please login or register.

Login with username, password and session length
Advanced search  

Author Topic: help designing a data base  (Read 798 times)

LordBaal

  • Bay Watcher
  • System Lord and Hanslanda lees evil twin.
    • View Profile
help designing a data base
« on: August 19, 2020, 11:27:48 pm »

Spoiler (click to show/hide)

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.
« Last Edit: August 20, 2020, 11:08:19 am by LordBaal »
Logged
I'm curious as to how a tank would evolve. Would it climb out of the primordial ooze wiggling it's track-nubs, feeding on smaller jeeps before crawling onto the shore having evolved proper treds?
My ship exploded midflight, but all the shrapnel totally landed on Alpha Centauri before anyone else did.  Bow before me world leaders!

Maximum Spin

  • Bay Watcher
  • [OPPOSED_TO_LIFE] [GOES_TO_ELEVEN]
    • View Profile
Re: help designing a data base
« Reply #1 on: August 20, 2020, 08:57:52 pm »

You have some kind of master code for each object, yes? It seemed that way from your original post. So what I would do is index the part table by a master code, then have a second table of synonymous codes, indexed by the synonymous codes, to master codes. So something like
SYNONYMMASTER
JD123ACME123
JD1BCACME123
EJDABCACME123
Where 'ACME123' is the master code that then indexes in your part database. For simplicity's sake an additional mapping of ACME123 -> ACME123 would allow you to lookup user input solely in this table even if the user inputs the master code. Then you just look up user input in this table, find the master code associated with the part, and then check the part table for it.

If you don't already have a specific master code for each part, the thing to do is select one.
Logged

LordBaal

  • Bay Watcher
  • System Lord and Hanslanda lees evil twin.
    • View Profile
Re: help designing a data base
« Reply #2 on: August 20, 2020, 10:12:50 pm »

Thank you very much Maximun! I reached the same conclusion this afternoon but your post confirm me that is the best way to go.

Again thankyou very much!
Logged
I'm curious as to how a tank would evolve. Would it climb out of the primordial ooze wiggling it's track-nubs, feeding on smaller jeeps before crawling onto the shore having evolved proper treds?
My ship exploded midflight, but all the shrapnel totally landed on Alpha Centauri before anyone else did.  Bow before me world leaders!

Maximum Spin

  • Bay Watcher
  • [OPPOSED_TO_LIFE] [GOES_TO_ELEVEN]
    • View Profile
Re: help designing a data base
« Reply #3 on: August 20, 2020, 10:15:31 pm »

I'm glad I could help. :)
Logged