The foreign key in the table is not in the database, with ORM

Question

I have a static entry table that is shared by multiple databases / website. By statics, I mean that data is read, but never updated on websites. Currently, all websites are served from the same server, but this may change.

I want to minimize the need to create / maintain this table for each of the sites, so I thought about turning it into a variable stored in a shared library that all websites have access to.

The problem is that I use ORM and use foreign key constraints to ensure the referential integrity of the identifiers used from this static table , therefore, removing this table from the MySQL database into a variable, how can I get referential integrity for the identifiers specified in this table? Do I have to do this completely programmatically or is there a trick to still process the data as if it came from a real db table.

My database definition (using Propel) initially looked like where the refer table had a linkto field that referenced the id the entry table. Now that the entry table is in the code, can I no longer use the foreign key trick or can I somehow get the same effect?

  <table name="entry"> <column name="id" type="INTEGER" primaryKey="true" autoIncrement="true" /> <column name="title" type="VARCHAR" size="500" required="true" /> </table> <table name="refer"> <column name="id" type="INTEGER" primaryKey="true" autoIncrement="true" /> <column name="linkto" type="INTEGER"/> <foreign-key foreignTable="entry"> <reference local="linkto" foreign="id" /> </foreign-key> </table> 

And of course, these other effective ways to do the same? I just do not want to repeat this table for multiple websites.

Answer 1

You can create a restriction - a powerful verbose one, perhaps if your FK id replaced by a link - but then you will still maintain this restriction for each website. This will help to find out that these tables are actually being tracked / used if you need suggestions on other ways to handle it.