I'm designing a database for a crm like software. Actually it's my first project so i'm learning on the run on this one.

I have customer and employee tables. I also have a history table for tracking the changes. But I want to ask, should I use separate history table for each master table? Right now I have customer, employee and contracts table. So, right now I have 3 separate history tables which have only ID difference, all other coloumns are same. If I will use one history table how should I determine for which master table the record was made?

Other question is about attachments table. I'll be saving files on server then copy the url to the attachments table. My question is same here, should I have three separate tables for employee, customer and contract attachments? If I will use one table how should I resolve the ID issue?

I have ID with prefix+auto increment in mind, but internet search says that it's a bad idea.

Thanks in advance..

Have a nice day..

PS: right now I'm just in a design stage. No SQL, just plain simple tables and relations.

  • What is the purpose of the history tables, are they part of a business scenario, or are they for tracking errors? Commented Jul 15, 2015 at 7:55
  • History tables are for auditing purposes. When and by whom data was added, deleted etc.
    – theo
    Commented Jul 15, 2015 at 7:56
  • @theo in one system we used one history table with the structure like (autoinc, orig_table, primary_key_value, orig_field, datetime, old_value) to track changes - it is good enough for auditing, you just pick the right primary key value and table pair. But it is not very nice if you should show history for all fields by default. For attachments, you can have one table for them and 3 junction tables to associate it with 3 entities. Or if each entity can have at most 1 attachment, you add column with attachment_id to employee/customer/contract, but you usually want more of them.
    – jkavalik
    Commented Jul 15, 2015 at 8:07
  • 1
    @jkavalik what are the benefits and cons of creating one history table? and how should I connect this history table to all master tables? About the attachments, thank you for the solution. I had something similar to that in my mind.
    – theo
    Commented Jul 15, 2015 at 9:23
  • @jkavalik if there will be 3 junction tables for attachment connection, wouldn't it will be better to use 3 separate image tables for each required master table? integrity vise I mean?
    – theo
    Commented Jul 15, 2015 at 9:30


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.