Hi all,

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,

1. Almost all tables are in 2NF (because of always using poor artificial primary keys (pk) there exists lot of transitive dependencies, for example volume_id-->book_no-->volume_no etc). No 3NFs.
po_book may have its own artificial pk, book_no, for example also ISBN if possible. A book has 0, 1, ... volumes. So pk of po_volume should be (book_no, volume_no). Yes, this pk consists of 2 columns.

2. Are there any foreign keys? If not, you should define them (use innodb for that).

3. Books without volumes? Give them volume_no = 0 (do not use NULL)

4. Enough normalized? I personally think no. There is one badly important reason you should have tables always in 3NF: You will simply reduce programming expenditures dramatically. 3NF means that there are no anormalies, no never-properly-function SQL select statements - especially joins, no awkwardly programmed patches on plain php, java, c++ level for concealing poor datamodels, etc.

5. At least you have designed enough tables (I meant that seriously)


krs,
tesu

Thanks tesuji,

I have revised my database to some degree based on your recommendation. Please see attachment for detail information. To open the second file, you need MySQL Workbench.

My focus is on the following tables:
po_verse, po_paragraph, po_volume, po_book and po_lang

As you can see, I have used foreign key with innoDB feature.

Here are my dilemmas:

1. There is no way to remove verse_id and paragraph_id artificial key. I might be able to use combination of three keys instead of paragraph_id but I don't think this is advisable. For verse_id, there is no way to find a unique key. Remember that verse_no is just the order number of verse within the same paragraph and it repeats with every paragraph. Do you still see a big flaw with this data model?

2. For translating verses, I decided to use the same table (po_verse) than creating another one. I got the idea from drupal database which they manage all translation within the same table. Let me know if this is not a good way of design.

3. For comment table, I can either put all comment for verse/paragraph in the same table or create different table for verse and paragraph. Obviously, if I create just one table and have a type column to distinguish them, I can't define foreign key anymore since it won't point the a single table. I personally favour having one table than having foreign key integrity. any comment? It's the same case for translations. I could create another table for each translation and have foreign key. (t_verse_id if translation_verse_id)

I really appreciate your help,