0

I'm designing a database for an application that should deal with notifications. Each notification has a sender, an arrival date and a processing status. There are three different types of notifications (application, substitution and affiliation) and each of these types has different additional data. It is very unlikely, almost impossible, that there will ever be a fourth type.

An application has the ID's of a position and a contract. A substitution has a title, an area code a subject code, start and end date and the number of countable days. An affiliation has a list of subject codes.

Here's my first approach with a child table for each. Primary Keys are marked with an asterisk. None of the fields would be nullable:

                        +-----------------+
                        | Notifications   |
                        +-----------------+
         +--------------| NotificationID* |--------------+
         |              | Type            |              |
         |              | Sender          |              |
         |              | ArrivalDate     |              |
         |              | ProcessStatus   |              |
         |              +-----------------+              |
         |                       |                       |
+-----------------+     +-----------------+     +-----------------+
| Applications    |     | Substitutions   |     | Affiliations    |
+-----------------+     +-----------------+     +-----------------+
| NotificationID* |     | NotificationID* |     | NotificationID* |
| PositionID      |     | Title           |     | SubjectCode*    |
| ContractID      |     | AreaCode        |     +-----------------+
+-----------------+     | SubjectCode     |
                        | StartDate       |
                        | EndDate         |
                        | CountableDays   |
                        +-----------------+

Is this a reasonable design or would it be more appropriate to have one table with most of the data fields nullable like this (nullable fields marked with -):

+-----------------+
| Notifications   |
+-----------------+
| NotificationID* |-------------+
| Sender          |             |
| ArrivalDate     |             |
| ProcessStatus   |             |
| PositionID -    |    +-----------------+
| ContractID -    |    | Subjects        |
| Title -         |    +-----------------+
| AreaCode -      |    | NotificationID* |
| StartDate -     |    | SubjectCode*    |
| EndDate -       |    +-----------------+
| CountableDays - |
+-----------------+
1
  • 4
    This is almost a classic normalisation question - the first type, different tables, is nearly always preferred.
    – pjc50
    Feb 16 at 9:34

Your Answer

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

Browse other questions tagged or ask your own question.