How can I model this in a relational database?

Question

I have a database for modelling a rental business. The database stores information for multiple Suppliers (owners) and each Supplier has a subset of depots. Each depot is closed certain days of the year, some are specific to that depot (might be closed every saturday or a whole week at thanksgiving) and others are global (all depots are closed christmas. I am trying to figure out the best way to model this.

My first thought was to have a DepotClosed table as follows:

depotclosed
   id (PK) INT
   start_date DATE
   end_date DATE
   display VARCHAR
   global BOOLEAN
   depot_id (FK)

My issue with this is that when it's a global holiday the depot_id will be null, but it's not "undefined", the holiday is just for all depots. Maybe I am just making more of a deal out of this than I ought to. Any thoughts would be welcome.

Thanks

Answer 1

My preference would be to explicitly record a closure for every depot (i.e., add a row to that depotclosed table for every depot). That way there's no ambiguity, and you can perform ordinary JOINs against the table.

Alternatively, you could just leave depot_id blank (i.e., NULL) and thus impose the understanding that when no depot is listed it applies to all depots. This way, however, you'd have to write JOINs something like this:

SELECT things
FROM depot
INNER JOIN depotclosed ON (depotclosed.depot_id = depot.depot_id OR depotclosed.depot_id IS NULL)

Answer 2

I would find that to be an acceptable solution. Just make sure that your application logic always check the global boolean before ever checking the *depot_id*. Also, make sure you do not have a constraint that requires a value for *depot_id* as a FK.

The alternative, as I see it, is to create a table for global holidays and this table for local holidays, minus the global boolean. This requires the same type of checks in the application, so either way would be fine to me.

Answer 3

I think this structure is fine. You can just select all rows where global is true. This would translate to two queries to find the closed dates for a single store, one for all closed dates for a single store, and one for all global closed dates. You had the right idea to avoid putting in global dates entries for each store: if something needed to change this would be a pain.

Answer 4

How many depots do you expect in total? If the answer is something like 100, you might consider just entering a row in the table for each depot in the case of a universal holiday. It wastes space, but it might actually save time.

The queries you will have to do for any given deopt will be real simple, since you won't need extra logic to test for global entries.