Friday, 27 September 2013

mysql - Many tables to one table - multiple entries

mysql - Many tables to one table - multiple entries

I have a system which has (for the sake of a simple example) tables
consisting of Customers, Vendors, Products, Sales, etc. Total 24 tables at
present.
I want to add the ability to have multiple notes for each record in these
tables. E.g., Each Customers record could have 0 to many notes, each
Vendor record could have 0 to many notes, etc.
My thinking is that I would like to have one "Notes" table, indexed by a
Noteid and Date/time stamp. The actual note data would be a varchar(255).
I am looking for a creative way to bi-directionally tie the source tables
to the Notes table. The idea of having 24 foreign key type cross reference
tables or 24 Notes tables doesn't really grab me.
Programming is being done in PHP with Apache server. Database is
mysql/InnoDB.
Open to creative ideas.
Thanks
Ralph

No comments:

Post a Comment