0

I'm dusting of my SQL, and would love to know if I've got my mental ER-diagram right.

http://sqlfiddle.com/#!2/c5c59

Basically, I've got 10-15 different datasets (actividades) from several locations (islas) over the span of a few months. My first instinct is to separate the many-to-many relationship between Islas and Actividades into an intermediary table, then reference(/index?) each dataset by location.

If this is the correct approach, I'm then wondering whether there is any benefit to have a separate date table (I guess this would be kind of like a calender). ie: the join between location, date and dataset would give one result, querying a date and location would give a list of all the activities that day and querying a single date would give all datasets from all locations.

Or should I just datestamp each datapoint (of each dataset) and deal with this in my queries.

I hope that was legible, thanks for your help!

0

It would have been great if your sqlfiddle data would have been in English...

If the activities aer bound to a single location, you could use your current approach. But if there's a chance that one activity can be referenced to more than one location you should use a separate table to show such a reference (using foreign keys to the primary keys of your activity and location tables).

A calendar table usually is very helpful if you'll need to return any results for a consecutive date range including date values without any corresponding values (e.g. activities).

| improve this answer | |
  • Sorry, English is a second language. – Andrew Aug 24 '14 at 7:32
  • There are seven locations, that share most of the activities, but not all of them. The approach you described is what I was trying to achieve with the intermediary table 'ActList', combining island ID and activity ID, perhaps I'm not referencing it quite right. – Andrew Aug 24 '14 at 7:43

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.