DB model for many tables with a zero-to-many relationship to the same table

I'm creating a DB schema for PostgreSQL 9.0 DB that will represent many real-world entities, with a table per entity. Most of these entities can have 0 or more free-text notes associated with them. A "note" also has some attributes, however, so it would need to be its own table - I cannot just use an array of strings. So, logically:

EntityA -> 0..* Note
EntityB -> 0..* Note
EntityC -> 0..* Note

What is the best way to model this in the database?

So far I've come up with 3 alternatives:

1. A column for the ID of each entity type in the Note table, with a foreign key. This is very ugly, because there are many entities.

2. A joining table for each entity table, joining it to Note. This is still ugly, because it doubles the number of tables and may also affect performance (extra join).

3. A generic "entityId" column in the Note table that logica开发者_如何学Clly refers to any of the tables, but is not enforced with a foreign key. I can also use the same PostgreSQL sequence for all the entity IDs so that an auto-generated ID is unique for all entities, not just entities of that type. This isn't fool-proof, though, because somebody could still insert an ID manually.

So far I'm leaning towards the 3rd alternative. It keeps the schema clean, but there is no referential integrity, which is not ideal. Could anyone suggest a better approach?

As a 4th alternative you could consider having a note table for each entity table (whose foreign key back to the associated entity table would be correct). The disadvantage of this is that you have more than one note table (one extra table per entity table, to be precise).

Is that going to be a problem? It can really depend on what is accessing this database. If it is an application, the application itself can be coded to determine the correct notes table to go with an entity table at any particular time.

I don't think this option is any worse than having a foreign key and a "which entity table" type field in a single notes table. At least constraints would be enforced.

Providing that:

• "Many" is a manageable number of entities (up to you to decide)
• There are some common columns among entities (like EntityName)
• One note belongs to one entity only, one entity can have many notes.

You could try something like

EDIT

In the next scenario, the Entity table has only EntityID and EntityType, while each E_Type_X table has all columns. The only purpose of the Entity table is to generate unique keys which are then propagated to each E_Type_X table. Because of the key propagation, each E_Type_X table can be directly joined to the Notes table.

This is a logical equivalent of all E_Type_X tables using the same sequence for the key generation -- so close to your solution No 3, but all foreign key constraints can be implemented.

I would go for option 2, as it is a more normalized structure, and I would not worry about the join.

If you are worried about it looking 'ugly' simply create a view.

In the end I decided not to create any new tables for this and created some rules instead. However, I'm accepting Damir Sudrevic's answer, because it's the closest to what I wanted (and I love the fact that he drew a diagram for me!)

Essentially there were two problems I wanted to solve:

1. Ensure that when an Entity is deleted so are all of its Notes.
2. Ensure that a Note cannot be created without referencing a valid entity.

The first problem was solved by creating a rule for each entity (which I could script if I get to enough entities):

CREATE OR REPLACE RULE rl_somentity_delete_note
AS ON DELETE TO someentity
DO ALSO DELETE FROM note WHERE entity_id = OLD.id

The second problem was not solved. I decided that I'd rather take the (low) risk of users creating "orphaned" notes than complicate the schema with an extra table and extra FK for each entity table, which would require an extra INSERT statement to add each entity row.