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.
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.
I wanna try get all my table design questions out of the way.
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.
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?