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
    Commented Jul 25, 2018 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
    Commented Jul 25, 2018 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
    Commented Jul 27, 2018 at 0:13

2 Answers 2


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

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

  • 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
    Commented Jul 27, 2018 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
    Commented Jul 27, 2018 at 10:08
  • (typo ... see modified entry below)
    – DanielMcQ
    Commented Aug 2, 2018 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
    Commented Aug 2, 2018 at 20:46
  • potentially the same two tables.
    – Jasen
    Commented Aug 3, 2018 at 2:29

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.

  • 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
    Commented Jul 27, 2018 at 0:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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