Is there a way to include a column from one table in many other tables (while maintaining consistency) in PostgreSQL?
I'm trying to build a database (in PostgreSQL 9.6.6) that allows for one "master column" (
items.id) to be replicated in to many (automatically generated) tables (e.g.
rank1.id, rank2.id, rank3.id, ...). Only
items will have
DELETE's) performed and when they are the newly added
id's should also show up (or be removed) in the
rankX table(s). To be more concrete:
items: id | name | description rank1: id | rank rank2: id | rank ...
id's are always the same, and there is always the same number of rows in each of the tables. The
rankX.rank values, however, will be different (imagine users ranking how funny a series of images are -- the images all have the same
id's but different users might rank them differently).
What I was thinking was that when a new user was added and a new
rankX table created I would do the following:
rankX.idreferencing a foreign key
ON DELETE CASCADE)
- Copy any
items.idthat already exist
- Auto-generate a trigger function that mirrors the
This seems cumbersome and wasteful of space since all of the
xxxx.id columns are identical and I will end up with hundreds or thousands of trigger functions. As someone new to relational databases I was hoping there was an easier way to achieve this.
So, I have a few questions:
- Is there a more efficient way to define my tables such that all of this copying isn't necessary?
- If this the best way, can you give an example of how you would set up the triggers (and associated functions)?
- Do I need to worry about running out of space on the server as I create (potentially many) sets of triggers of this type?