I'm trying to design a database model for some ancient poetry books. Here are more information:
- Each verse of book will be a recorded into database. This is critically important since I need to have different feature for each verse. For example, being able to put comment per user, translation, highlighting per user, commentary for organizations per verse and etc.
- I divided books into the following categories:
Book --> Book table
Volume --> Volume table: If a book has more than one volume, this table will be consulted
paragraph --> paragraph table: List of paragraph in each volume/table
verse --> verse table: Each verse is comprised of two line. (mesra1 and mesra2). verse_no is the order number of each verse in paragraph and verse_id is the unique primary key.
Comment is per user comment for each verse and commentary is for organizations
- Database is MySQL v5.x
- See attached DB schema file for more information.
And here are my questions/dilemma:
- My paragraph table is not fully normalized. I have volume_no and book_id column and not all books have volume. What is the best way to address this issue?
- For each verse's translation, I haven't created a separate table for verse translation and used the same table. The new column is: t_verse_id. If the verse is in source language, this value is 0 and if it's not, this will point to verse_id on the same table. Is this the right approach or it's better to have separate table? Keep in mind that if I create a separate table, most of the attributes will be the same. I modeled this translation from Drupal database schema.
- Is this data model normalized enough?
Thanks in advance,