2

Suppose you have 2 different entities:

  • Entity 1: PersonalNote - a note you create for yourself
  • Entity 2: SharedNote - a note you share with someone else

A PersonalNote can also be viewed as a SharedNote that is shared with only yourself. So the database schemas for both of them can be identical or almost identical.

Is this enough of a semantic difference to justify going for a separate database table each or is it better to combine the two into the same database table since their schemas can be made to be identical (with one NULL field for one of them)?

One downside to 2 tables would be having a lot of similar looking code and almost identical but separate data classes. One downside to 1 table would be performance I suppose.

I've been thinking about this for a while now but I just can't decide which option to go for. And maybe I'm missing something crucial as well. What's the best choice here?

3
  • Why would you share something with one person and only one person? Dec 2, 2021 at 0:15
  • @MichaelKutz a bit like private messages. but good point, i suppose you are suggesting if i want to eventually enable group sharing then it might make more sense to separate? Dec 2, 2021 at 0:24
  • @MichaelKutz btw you can ofc share the same note with multiple people, one after the other, in case thats what you meant. just no group sharing. Dec 2, 2021 at 9:28

2 Answers 2

10

To me it sounds like you have one entity / object, a Note. You can have a NoteType or IsNoteShared field to differentiate between if it's a personal or shared note.

Or you can even get away without an extra field to differentiate the two by instead creating a second table called UserNotes which stores just the NoteId from the Notes table and the UserId from the Users table (that you presumably already have). If a record is in the UserNotes table then that means that note is shared, otherwise if it's not in that table, then it's only available to the user who created that note by the CreatedByUserId field in the Notes table.

The benefit of designing it this way is because the UserNotes table allows a way to relate the many-to-many relationship between Notes and Users should you want to allow Notes to be shared to multiple Users.

Example DDL

CREATE TABLE Users 
(
  UserId INT PRIMARY KEY, -- auto increment/identity
  UserName VARCHAR(50) NOT NULL
);

CREATE TABLE Notes 
(
  NoteId INT PRIMARY KEY, -- auto increment/identity
  CreatedByUserId INT NOT NULL REFERENCES Users(UserId),
  NoteText CLOB
);

Create table UserNotes 
(
  -- both are defined as NOT NULL via PK
  UserId INT REFERENCES Users(UserId), -- who else can see this
  NoteId INT REFERENCES Notes(NoteId), -- what Note they can view
  CONSTRAINT UserNotes_pk PRIMARY KEY (UserId, NoteId)
);
7
  • I like your approach, but what do you say about performance concerns if I dump all PersonalNotes and SharedNotes into the same table? Note that when sharing the note a copy is made so both users each own a copy. Wouldn't that single table explode and drag down performance? That's the main reason I hesitate going for a single table only. Dec 2, 2021 at 9:26
  • 1
    A million rows is nothing to a modern database. Besides, you'll be storing only 1 copy of each note ( Notes) and 1 row per non-owner (UserNotes) Dec 2, 2021 at 10:49
  • Perosnal Notes and Shared Notes are both subclasses of a superclass, possibly named Notes. Subclasses or Subtypes have been discussed several times in this area. Dec 2, 2021 at 11:28
  • 1
    @csstudent1418 Good rule of thumb is to design things appropriately to your use case and standards first, optimize (if needed) second. As Michael mentioned, modern database systems can handle a lot of data. I've worked with tables that have had 10s of billions of rows, and could query them for my use cases in less than a second to at most a few seconds, because they were indexed appropriately.
    – J.D.
    Dec 2, 2021 at 12:12
  • @MichaelKutz Thanks for adding in some example DDL scripts. Just so I can clarify for readers (since OP didn't tag a specific database system), is the syntax you used from Oracle PL/SQL?
    – J.D.
    Dec 2, 2021 at 12:22
0

Can you share your PersonalNote to make it SharedNote? Can you unshare SharedNote (but not deleting it, keeping it available to yourself, therefore making it PersonalNote). At least the first one is something I would expect to be able to do, unless there's some good reason to not to. And what happens in such case? Do you move data from one table to another? That might be potentially expensive operation, and introduces some possibility for error, while flipping single bit (shared or not) is much cheaper and easier. It's good you don' want to introduce nulls, but you don't have to, just have list of people with whom the note is shared (I assume that note is always shared with its owner).

2
  • 1
    1. Yes you can share a PersonalNote. In fact that's the only way to create a SharedNote, by sharing a PersonalNote. 2. No you cannot unshare a note once it was shared, as the SharedNote will be a copy of the original, thus being owned by the addressee. Data is not moved, rather copied, when sharing. In other words A creating a SharedNote for B is like B creating a PersonalNote for themselves. It's correct that note is always shared with its owner Dec 2, 2021 at 9:04
  • That's quite interesting. In that case I might even consider different structure, instead of having two tables (PersonalNote, SharedNote) or column with flag whether the note is personal or shared, what about having entry in table per note owner? So if A shares note with B, there will be two records in the table (A, note), (B, note). If notes can get big, then maybe having different table for note content and table for metadata might be considered.
    – Malky
    Dec 2, 2021 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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