2

I try to create a database design to suit my needs.

I would really appreciate any of your recommendation for my following table design:

enter image description here

I have two components: Categories and Content.

Whenever I create a new item for any of those two components, a new route item should be created also, with relations like shown on visual diagram.

Red and Blue highlights are identical with their colors.

Is it good practice to have the following columns in those tables like ci_routes.refid, ci_categories.slug and ci_content.slug ?

==== UPDATED ====

The business logic for my app works like this:

  • I create a category item with html form
  • On form submit, app will create a new route to return a newly created ci_routes.id
  • Category will be created and filled with all info including ci_categories.rid.
  • Then it updates yet again the ci_routes with the all information where the ci_routes.slug is identical to ci_categories.slug and ci_routes.route will be based on configured setting something like $path/$category_id where $path = 'category'

Whenever I decide to change $path to something like $path = 'node', all my routes should be purged and recreated based on new settings.

Pls correct me If I made some logical mistake somewhere in my logic

aspirinemaga
  • 3,753
  • 10
  • 52
  • 95

1 Answers1

1

Is it good practice to have the following columns in those tables ci_routes.refid

No. In the current design ci_routes.refid will be a column referencing two tables, you can not have a foreign key to more than a table on a column in RDBMS, so you have to handle it in your application logic, that would be a buggy, hard work. Also you will loose data integrity in database level, that is not appreciated.

Solution 1:
Have two separate columns in ci_routes to reference ci_categories and ci_content like ci_routes.content_id and ci_routes.categories_id

Solution 2:
Use inheritance by having a base table for components and categories, containing shared properties of these two entities, having the base table you can refer it inside ci_routes as the foreign key.

enter image description here

Is it good practice to have the following columns in those tables like ci_categories.slug and ci_content.slug ?

Answering the question needs more information about your business logic.
BTW it seams you are going to de-normalize your model by redundant slug column. If you are going to do this just for performance reason then think about when a components slug column is updating, you will need to update dependent rout row(s). I won't do that until having specific evidences.

Community
  • 1
  • 1
Mohsen Heydari
  • 7,256
  • 4
  • 31
  • 46
  • Thank you for your answer Mohsen. The reason I have slug in both component tables is that if someday I decide to truncate the routes table to generate new values based on new settings, it will be a lot easier I guess. But maybe I miss something. I will update my question in a moment, to give you a better perspective about my business logic. – aspirinemaga May 29 '14 at 13:51
  • What will happen if you persist slug just inside Category and Component and removing it from routes? – Mohsen Heydari May 29 '14 at 14:36
  • i was wondering about the same, either left it inside `routes` and remove from components table, or like u said, remove it only from routes. – aspirinemaga May 29 '14 at 15:26