Currently I have a relationship between contact and time_log (contact is a verb here - could be a meeting/phonecall/email). Time_log holds both a date and time, each contact is associated with one date and amount of time spent on that contact. I also have a list of several contact_services_provided at each contact. This was fine until I learnt that time spent on each of the specific services provided at a contact was also required. I thought of linking time_log to contact_services_provided instead of directly to contact, but then the date is repeated for each service provided which is not correct (there should be only one date per contact), so I'm not sure how to proceed. Also, I've added a sad image of my user interface and associated database design which might help to visualize the problem (you'll have to zoom in...)


Can't see the attachment, but if I understand correctly, any one contact can only have one date. Each contact is made up of (potentially) more than one contact_services, each having it's own duration. In this case, a suggestion would be:

duration (of the contact)

contact_service_type_id (or something along those lines)
duration (of the service)

Elaborate further if I am misunderstanding.

  • Yes, each contact is made up of a number of contact_services, each having its own duration, but if you add up the times of each entry in contact_services, the sum equals the time of the contact as a whole. So, contact does not need a duration, right? Also a question - in this design you have dropped the "time" table. If another table (activities) also has a time associated with it, should time be recorded in the activities table, or would you reinstate the "time" table to hold data from both contact_services and activities in one place? – 3rdTimeLucky Feb 14 '17 at 23:58
  • Correct, contact wouldn't need a duration as such, however, as the table grows, you may wish you doubled up there. For example, if you want to query the duration of all contacts, that's going to be faster if you only have one table to deal with. It could also be a good "double checker", in case some services didn't get recorded properly, resulting in excess time in the contact table. – kezsto Feb 15 '17 at 1:27
  • As for time, if it's not an entity as such, it shouldn't need it's own table. It's just a value right? – kezsto Feb 15 '17 at 1:28
  • Time is just a value, yes, but I thought having it all in one place might make reporting easier. But, so far, its just in two places, so maybe that's OK. As for contacts, I don't worry too much about speed, we aren't talking about massive amounts of data. – 3rdTimeLucky Feb 16 '17 at 6:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.