CODEWITHSUNDEEP
mysqlsqldatabase-design
rf_circ
rf_circ

Reputation: 1915

How to design a table that can either reference another table or be a custom entry

I have a website with many different brick-and-mortar businesses as clients.

I need a table that holds "closed" dates for each business.

My initial design is to have all of the usual US Federal holidays listed in a table, which I then reference via foreign key from the master table of closed days. That makes it very easy to put checkboxes in the UI so they can easily check off the federal holidays.

But I also need to support custom closing dates.

My intial design is as follows:

+-------------+
| close_dts   |     +-------------+     +--------------+
--------------+     | holidays    |     | holiday_tags |
| id          |     +-------------+     +--------------+
| holiday_id  | --> | id          |     | id           |
| custom_date |     | holiday_tag | --> | tag          |
+-------------+     | date        |     | name         |
                    +-------------+     +--------------+

Where

What I'm basically asking here is how to handle the situation in which I would normally like to link to another table of standard items, but also occasionally define a custom item that doesn't link to anything.

This is MySQL, if it matters.

Thank you.

Upvotes: 0

Views: 59

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Your design is okay. You could add a constraint so that always only either holiday_id or custom_date is filled.

You can, however, make your model even simpler:

+----------------+
| close_dts      |     +--------------+
-----------------+     | holiday_tags |
| id             |     +--------------+
| holiday_tag_id | --> | id           |
| close_date     |     | tag          |
+----------------+     | name         |
                       +--------------+

With holiday_tag_id being nullable.

Upvotes: 1

Related Questions