0

I'm designing a database where a user can add notes on diffrent entity of the application. For example, the user can add a note on the project and/or on the project line. The note added can have different type, even if are on the same entity.

For example, I add a note on project, I can mark it as "Customer follow-up".

When analyze of other phases for the application will come, they will probably be more note type or more entity that the user will have the possibility to add notes.

For now, I have a table "Note" as follow:

Id
NoteTypeId
Note
CreatedBy
CreationDate

Since it is a relation of one to many, I was thinking about doing a join table "ProjectNote" and "ProjectLineNote". If other entity can receive notes, I will add new NoteType and a new join table "[EntityName]Note".

Does it make sense to centralize all notes in a single table and have multiple join tables ? Should I instead put the text in the "ProjectNote", "ProjectLineNote", and not have a "Note" table ?

1
  • 1
    "a relation of zero to many" Please fix the typo. At a given point in time we may look at a one-to-many relationship where the "many" takes on a value of "zero items". Perhaps that's what you intended? // Also, here's my tuppence. I favor your "put the text in ProjectNote, ProjectLineNote tables", in the interest of simplicity. One can always copy the text into the other table if user demotes or promotes its level.
    – J_H
    Oct 16, 2023 at 20:58

1 Answer 1

2

The short answer to your question is: It depends upon your use cases.

If every time you lookup notes, you always do it in the context of a particular entity - i.e. I want the notes associated with Project(id:4) then having seperate tables for the notes associated with each entity type (Project, ProjectLine, etc) makes sense, since you don't need extra where clauses/indexes and all queries will be automatically limited to entities of that type. However if you have a use case that you need to return all notes created by User(X) then you probably want to put all the Notes in one table.

If you have a use case where you need to attach a single Note to multiple entities (it doesn't matter if all entities are the same type or different types) then you will probably be forced to use at least one ** join table since you have a many-to-many relationship.

** - You could use multiple join tables (for different entity types) which allows you to enforce referential integrity. Alternatively you could use a single join table containing the fields (EntityType, EntityId and NoteId).

However if you only have a 1-to-many or many-to-1 relationships, I would be strongly tempted to avoid adding additional join tables - depending upon your other use cases you may have to sacrifice referential integrity (specifically, if you only have one Notes table for all entity types you probably won't be able to create a foreign key) - you will have to decide if that's a problem for you.

2
  • Ah yes I didn't think about the fact I would not be able to do the foreign keys using only one "Notes" table. And I don't think we will have a use case where we would want to show all notes for a specified user. I will go with seperate tables for notes associated to each entity types.
    – mp9007
    Oct 17, 2023 at 12:54
  • If you're using PostgreSQL you may want to check out table partitioning postgresql.org/docs/current/ddl-partitioning.html I haven't used it myself, but it looks like you could partition based on entity type so that the individual base tables have foreign keys, but all the type specific tables are aggregated into a single top level table.
    – DavidT
    Oct 17, 2023 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.