Need some database design help... 2

[rtgordon]

I am not sure if this is the correct forum, but I am in a bit of a pinch, and need a little advice. I have been given a challenge by my VP. He wants me to design a part number database that will allow a user to enter a part number (it can be our part number, a supplier part number, or a competitor part number) and return all the part numbers from suppliers, competition, etc. that relate to our part. Similar to a cross reference file.

The data that I will get will probably be a simple spreadsheet where each row represents a part, and each column represents each other part number that can reference that part.

After doing some head scratching, I came up with a solution, but I want to make sure that I am on the right path.

I am thinking that I would have one table that would serve as a "part master table". It would have every part number enterable by the user and our corresponding part number. Then, in another table, I would have our part number, and all the corresponding associated part numbers and information. Maybe something like this:

Table1
part_nbr
our_part_nbr

Table2
our_part_nbr
mfg1_part_nbr
mfg2_part_nbr
mfg3_part_nbr
comp1_part_nbr
comp2_part_nbr
comp3_part_nbr
vend1_part_nbr
vend2_part_nbr
mil_part_nbr

What I am concerned about is that there is a better way that I am not aware of. I thought about making a table for each supplier, competitor, that all tied back to ours, and sequentially searching them, but that would not be a good way to approach it. The only problem with my current solution is that I would have potentially several unique part numbers that reference only one of our part numbers. I am only looking for some high level design hints here.

Thanks,
gordon

 

[fluteplr]

Consider this.

One table that holds all the part numbers, yours and your competitors with a partnumber field, a company field and any other other descriptive info you need and an id field (partid). The id field is for internal use and should not really mean anything. You can just number them sequentially.

A second table has two columns, your partnumbers id (partid) from the 1st table and the competitors partnumber from the first table (relatedpartid). If there are 6 related parts for one of your parts there would be six entries.

The query to find all the related parts would then be to
select all the items from the second table whose partid or relatedpartid = the partid of the item from the first table.

 

[rtgordon]

So, what you are saying is that the data might look something like this???

Table1
part_id		our_part_nbr	mfg1_part_nbr	mfg2_part_nbr	comp1_part_nbr
1			123				ABC				DEF				GHI

Table2
part_id		related_part_nbr
1			123
1			ABC
1			DEF
1			GHI

 

[wushutwist]

How about:

Table 1:
part_id
part_number

Table 2:
part_id
company_id
part_number

Table 3:
company_id
company_name

Example Data:
Table 1
part_id part_number
1 123

Table 2
part_id company_id part_number
1 1 ABC
1 2 713
1 4 lkj

Table 3
company_id company_name
1 mfg1
2 mfg2
3 mfg3
4 comp1
etc..

You could really merge table 1 and 2 but it may be easier for development to just keep them separate and break normalization just a little.

 

[fluteplr]

No, table one would look like this.

partid partnumber company other stuff
1 abcde acme this is a widget
2 13435 mycompany this is a watset

If you do it the way you said, you will have to change the table structure every time there is a new company.

 

[wushutwist]

Table 1 was assumed to be the mythical "Our Company" table. Like I said you could put table 1 and table 2 together and treat all companies the same. This is better Design but the other way is a tad easier on development, either way they are both simple.

 

[rtgordon]

and the light is slowly getting brighter

Thanks for the suggestions. I will try and let them sink in a little bit. I like what I have read so far. It took me a minute to catch the jist of the second suggestion, but now it is making more sense.

Thanks,
gordon

 

[dbadmin]

The best way to actually handle this issue is to use a many-to-many relationship where the intermediary table contains only the partID, and the related PartID... Since this is a self-referencing table, only two tables are needed...

Part_Info Parts_Related
--------------------- ----------------------------
PartID PartID
PartDesc RelatedPartID
PartMfg
etc...

Example:
--------------

Part_Info: Parts_Related
--------------------- ----------------------------
1 Widget Comp1 1 2
2 Watset Comp1 1 4
3 Somedat Comp2 2 4
4 Other Comp3

and so on... now you have all the flexibility to search for a part number and get all related parts in a recordset.

Hope this helps...

 

[fluteplr]

Gee, Exactly what I said about 5 posts ago.