How do you structure your entities you intend to reuse?

Answer 1

The way i've usually seen this handled is in option 1. The up-side is that this is a fairly easy way for the humans involved to see what is happening. As you said, the downside is that the SQL becomes a bit less elegant.

That's just kinda how it goes, though. In reality, even though it may feel a bit clunky, the resulting queries are clear and readable. You will only have to add sql to new code, as you go, in the areas where you've added new tables.

When you're actually manipulating notes directly, you only have to handle one table.

Answer 2

There is a fourth method (which I would recommend): add a unique id to the Note table, and a field noteId to User, Client and Project

Answer 3

What do do may depend on whether the notes fields you need are truly the same between entities. If they are the same then, I would probably have a Notes table (then if the needs change all notes get a revision date field, for example, added in one step) with a Notetype (user, client, project), noteID, and id from the appropriate table as well as all the notes related fields you need. The biggest problem with this is the FK relationships will have to be managed through a trigger instead of an FK. You don't want to put in an invalid userid, but since the whole column doesn't contain userids you can't just set up an FK relationship. On the other hand if you add another entity later, you can do without affecting your table structure (although you would still have to modify the trigger) which is the problem with having the ids in separate fields. This also means you will not be able to use cascade update or delete which can make deleting records (or changing a natural key (shudder)) more difficult.

If the needs for what fields you have for the notes are different between entities, I would create separate Notes Tables for each entitity.

If you are going to hit the Notes table in every query between all the enitites, you may want separate Tables for each entity as well to reduce locking and blocking issues. Notes however, may not need to be hit as often so it might not be too bad a situation.

Answer 4

Option 3. Have an identity as primary key, and have a foreign key column for any tables that share the Note table. I handle Email, Phone, Address etc. this way.

The only drawback I know if is if you have 10 tables linked to Note - you'd have 10 foreign key columns.

The problem I have with Option 1 is that it feels like you are creating an artificial foreign key (with TableName/Pkid, which isn't actually part of the User/Project primary keys, which are only Pkid). That may just be my personal bias, I just never felt like that was good design, but I couldn't exactly say why.

Answer 5

I would typically go with option 2. I don't stress about the number of tables in a database within reason.

The big question is, are these notes going to be handled as a single item? For example, are you going to ever say, "Show me all notes, regardless of whether it's for a client or user or project." I can't think of any situations where that would be useful. You're going to typically say, "Show me THIS CLIENT'S notes" or "Show me THIS PROJECT'S notes."

Since they are not logically one item I suggest not trying to model them physically as one item.

Answer 6

I think, you should definitively avoid solution 1 in this case.
You loose the integrety reference, it becomes impossible to query your database without dynamic SQL and you always need 2 queries to select some notes!
You should never use this way in simples cases like here.

As I see it, I go with option 4 (adding a field note_id on project, client and user). If you need multiple notes for one row, it's not a problem. You simply add an extra table (lets say note_set) that makes the n-1 relation.

client(client_id, note_set_id) user(user_id, note_set_id) project(project_id, note_set_id)

note_set(note_set_id) note(note_id, note_set_id, title, body)

the major problem of this solution, is that given a note you cannot find the initial "entity" without making an UNION on the three tables. It doesn't seems a big problem to me but, if it is, then go with option 3.

Finally, option 2 is good as well but it's not what I call "reuse".

Please excuse my english.