0

Is there a standard pattern for linking products to manufacturers, when we want to capture the fact that manufacturers are interacting with each other over time (mergers, takeovers, etc)?

In our industry, company A makes product 1, and many people refer to that product with a token like "A-1" when talking about it or trying to look up detailed performance and physical characteristics of that product in our database.

If company B purchases A, we still want to be able to allow people to use a token like "A-1" to look up the performance data of that product, even though the owning company is now B. A new user meanwhile can look at the records and understand that company A made the product and is the former owner, while company B is now the "real" owner.

Is it viable to model this with two manufacturer foreign keys, and a version column that would increment when the manufacturer relationship changes? The version column would be used to allow the older "A owns 1" row to remain in the table for existing records that linked to it at the time A owned 1 before being bought out by B.

enter image description here

  • 1
    Is the history meant to only capture manufacturer changes or changes of products as well? In other word, if a products characteristics change (in the real world) should the changes be reflected in all the rows for a product or only the one with the current manufacturer? – sticky bit Jul 25 '18 at 22:00
  • Are we to assume that a part can only have one manufacturer or supplier? I am thinking this may be addressed by creating history tables at different levels in the database but need more clarification of the existing structure. – beccasue Jul 25 '18 at 23:18
  • A product can have one "owner," which could also be the manufacturer, or a "published" manufacturer that's different from the "owner." This would handle the case where company B buys A and now owns product but want's to keep producing product under subsidiary A. No other manufacturer relationships than that (e.g. can't have more than one owner or published manufacturer) – DanielMcQ Jul 27 '18 at 0:13
0

Use a join table with a date of validity

product_id           refernjces product(id),
manufacturer_id      references manufacturere(id),
start_date           not null default CURRENT_DATE
end_date 

you'll probably need several indices depending on what reports you want from the database.

| improve this answer | |
  • Wouldn't this also need a "relationship_type" column to denote "owner" vs. "published manufacturer" vs. perhaps other types that arise in future? I guess this approach would probably be good for handling unknown future relationships. – DanielMcQ Jul 27 '18 at 0:17
  • if you need to track those things make a new join table.. (I'm not a fan of anything approaching EAV) – Jasen Jul 27 '18 at 10:08
  • (typo ... see modified entry below) – DanielMcQ Aug 2 '18 at 20:44
  • But isn't "owner" vs. "published manuf" a quality of the first join table you suggested? What two tables are you thinking would be joined by this second join table? Thanks for any clarification, Jasen! – DanielMcQ Aug 2 '18 at 20:46
  • potentially the same two tables. – Jasen Aug 3 '18 at 2:29
0

This is how I might approach this design... by adding an ID for the original manufacturer at the Product level, then storing the details in a history table.

The history table would allow for Manufacturer IDs (or other details) to change over time to be stored without a need for version numbers.

Product table

Product_ID Product_Name Orig_Mfgr_ID

Product History table

Product_ID ProductHistory_ID Orig_Mfgr_ID Mfgr_StartDate Mfgr_EndDate CreateDate

I would then create a view or function to pull the most recent/current manufacturer based on the purchase date of the product, rather than storing this information in the Product table itself.

| improve this answer | |
  • Yes, ok. I think this is similar to the idea directly above by @Jasen. I guess this works as long as the history table can have contemporaneous entries to represent two current relationships: an "owner" manufacturing company and a "published manufacturer." (Use case above where B buys A but wants to keep using A as subsidiary to make product.) – DanielMcQ Jul 27 '18 at 0:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.