Trying to create a relationship with time - Help!

Hi @chrisgreenslade,

Just my 2 cents but your times_of_enforcement table is 58 columns wide and it’s structure isn’t great for analysis, if you ask me. From a quick overview it seems there are a couple of things to take into account: Months, Days and Times, so a structure like this seems more suited.

Starttime and Endtime keys can be linked to the Time dimension table but that’s not required, you could also do that virtually. As for Month and Day I would recommend creating a virtual relationship with a Date dimension table (oddly enough that seems to be missing from your model entirely…)

Here’s code for a Date table to get you started.

And I fully agree with @adam before you proceed further take some time to clearly define exactly what you want to achieve.

1 Like