CODEWITHSUNDEEP
phpmysqlrelational-database
praet0ri4n
praet0ri4n

Reputation: 53

PHP + MySQL - one table holding reference to IDs from multiple tables

I just would like to know what are the most common approaches to get a table to hold a reference to IDs from multiple tables.

I have a system with modules like customers, suppliers, orders, etc. and I would like to add a "Notes" functionality to all of those modules to be able to add/read notes.

As one customer/supplier/order can have multiple notes, I have chosen the one-to-many relation way and so the notes in their table should refer to the particular item id in a separate column. But as I will refer to IDs from multiple tables, their IDs will be overlapping and I need a way to say in which particular table to search for that ID.

I don't want to create exact the same notes module for each of my modules and here I could concentrate notes in one table. Those notes differ only in the fact, to which module they belong to.

Shall I

Thanks for your ideas!

Upvotes: 0

Views: 129

Answers (1)

Alan Horrocks
Alan Horrocks

Reputation: 324

We do something similar with notes that can be attached to many objects. Each of our objects has a unique class id (we store each type of object in it's own table), and we store the unique class id + specific object id in the notes table.

We then just have to maintain a lookup of unique class id -> table name. By using the unique class id + object id as the key we ensure that the same id in different tables isn't an issue.

Upvotes: 2

Related Questions