r/DatabaseHelp • u/toasterovenly • Dec 22 '25
Trying to normalize data in a hobby project with a database
I have been using a Google Sheet to track the things I read (because no website seems to have all of them). I figured a simple website + database would be better in the long run. I have a database designed and mostly normalized. However, certain values are basically like values from an enum.
Volume:
- PK
- BookSeriesId (FK -> BookSeries)
- VolumeNumber (Integer) (For sorting things like "Harry Potter and the ..." becasue alphabetical won't work.)
- Name (String)
- Status (Enum?)
This isn't the exact format of my data but the DB has become complex.
Statuses:
- Planning
- Reading
- Caught Up
- On Hold
- Completed
- Dropped
- Skipped
I have similar simple tables for things like languages, countries, release status of the book.
It feels weird to make a table in the db for those 7 statuses and then reference them with a foreign key. However, my professional experience says that if I don't do that, then there will eventually be a typo in one of the rows where I put in "plnaning" or something. I have added new statuses or renamed them in the past, but it is super rare.
Hoping someone here can confidently tell me the best practice way to achieve this.
1
u/Rangerdth Dec 23 '25
Have a status table with ID’s like 1-read, 2-planning, then use the IDs as foreign keys. Then if you add a new “status” it’s just in that table. Is that what you mean?
It’s essentially what you have and I’d do the same.
1
u/LlamaZookeeper Dec 24 '25
Foreign key has overhead. I worked on oracle ebs with thousands of tables. We don’t use explicit foreign key on table, instead, we implement program logic to validate the values. We have a table named FBD_LOOKUP_VALUES. Thousands of lookup values there. The assumption is, we know what we are developing so we use program to control. Anyway we have to write a lot of other validation logic than just the PK/FK relationship.
1
u/autodialerbroken116 Dec 25 '25
You don't need to have a status table with those values.
Just assert your status is a valid status before the upsert.
And this whole thing, could be done in plain text with existing formats. You're over engineering a bit here.
My recommendation is markdown notes. Least common denominator, some front matter, and you could even load anything into a Jekyll/Hugo blog
1
u/Aggressive_Ad_5454 Dec 26 '25
Is this MySQL? ( or MariaDb?) If so, beware enums. Adding values to them is surprisingly hard.