I have some questions regarding these tables.

Question 1:

Is it best to have the design like this, ie different tables for each type linked with a foreign key or would it be better to just have the main table and on implementation just use an ENUM to offer the possible choices for each type? Welcome any suggestions.

========================
Notification_Table
========================
Notification_ID
Person_ID
NotificationType_ID
NotificationSubType_ID
Notification_Details
Notification_OpenDate
Notification_CloseDate
Target_Date
StatusType_ID
========================


=======================
NotificationType_Table
=======================
NotificationType_ID
Notification_Type
=======================


==========================
NotificationSubType_Table
==========================
NotificationSubType_ID
Notification_SubType
==========================


========================
StatusType_Table
========================
StatusType_ID
Status_Type
========================

Regards

Wealth-Builderz

the "lookup" tables are by far the better design

ENUM is evil, don't drink that koolaid!!

In principle r937 is right. However in , very very, few circumstances an enum may be a practical design. imhv it works if you can at design time be confident than you can capture all possible states. If you can then an enum is practical, but in practise I've always used tables as r937 suggests. I'm no propoellor head and therefore never feel entirely happy that we can define all states to use an enum.

Thanks for the advice.

I wanna try get all my table design questions out of the way.

Question 2:

In the Notification table shown below I need to store two Person_ID's, one pertaining to the Customer (submitter) and another one pertaining to the assigned Resolver (respondant). How should/would I go about modelling this in the design. I wouldn't have this trouble if I had two separate tables ie Customer and Resolver but I've been reading that it is best to keep all the Person like objects/attributes in one entity and then subtype the types of Person for ease and flexibility of maintenance. Again any advice would be most welcome.

========================
Notification_Table
========================
Notification_ID
Person_ID
NotificationType_ID
NotificationSubType_ID
Notification_Details
Notification_OpenDate
Notification_CloseDate
Target_Date
StatusType_ID
========================


========================
Person_Table
========================
Person_ID
First_Name
Last_Name
Username
Password
PersonType_ID
========================

========================
PersonType_Table
========================
PersonType_ID
Person_Type
========================

you are correct that all persons should be in one entity, with attributes to distinguish their types

speaking of types, the existence of both type and subtype in the notification table is suspect

is there a relationship between type and subtype? if so, the design fails second (i think) normal form

Ummm, I'm new to databases, I'm not entirely sure.

One of my project requirements is to produce a report showing the number of complaints by type and sub type over a given period, hence me creating a 'Notification' table supporting these two individual types. How would you suggest going about it, always welcome any advice which helps me learn?