Optimal design in managing Contacts and Availability.
I am designing a relational model that supports the implementation of a Notification System.
The main scenarios where I have some doubts are:
Be able to cover a time availability from an "Entity" in order to be notified, ("Entity" is probably not the best name, but I modeled it this way for a reason which Ill explain later).
Be able to contain different "Contact"s belonging to an "Entity" (Entity has many Contact, and a Contact belongs to one Entity) , visualizing a priori the following "ContactType": Telephone, Cellphone, Beeper, and Address.
Under this point of view, I have a doubt about how optimum this vision is of containing, in only one table "Contact", the data such as: telephone, cellphone, Beeper, Address and so on (which could come in the future).
I created this design, because I modeled a "Generalization" about the "Entity" concept since it is the highest level entity concerning the lower level entities group; in this case are "Teacher", "Director", "Student", "Representative".
All these entities mentioned before will manage "Contact" that could be: Telephone, cellphone, address and so on) and some "Contact" (depending on the ContactType + The EntityType) will manage "Time Availability", for example ContactTytpe = Cellphone and EntityType = Student, it could have a
scheduled for being notified, It is not the case for a ContactType = Address which will have just the address of the entity. Basically ContactType equal to telephoneNumber, CellphoneNumber and Beeper are able to be notified.
Finally, how optimum is it having a table that manages the Contact information the way I proposed? At least considering the generalization, it seems very clear graphically, avoiding circular dependency.
Basically depending on the "ContactType" is how the data will be inserted in the "Contact" table. For example; if Id like to add a new telephone contact for the entity 'Director', all the fields unrelated to this concept will be equal to NULL (address, State, Provincy, ZipCode, cellPhoneNumber, celphoneCode, BeeperNumber,...)
What are the advantages and disadvantages of making a design in this way? (I know some of them, so I just want to re-confirm them. ¿ Is there a better optimized proposal?
And my other doubt is about which the best optimized way could be in order to be able to manage the "Time Availability", considering that some contacts (depending on the type of contact + the type of the entity) will manage "Time Availability".
I thought of an entity named "Availability" which will contain a range of time that contemplates the entire day (for example tuples of 15 minutes x the seven days of the week), however the number of final records of the entity which will cover the Contact v/s the Availability (middle table between N:N relation) will be equal to the number of each time block x the number of the days of the week x number of the contacts with a Availability time active, so I can envision that this table will have a very large size.
Hopefully I wrote clear, Thank you in advance for any comment!
I have analyzed your diagram and I have some doubts. I attached my last review model in order to send you my comments and doubts in relation with the proposal diagram that you drew for me (thank you).
At first, I thought I needed just one table called "Contact" in order to insert Address and Telephones, but finally I disregarded this option because I think is clearer have two different tables (Location and Contact) and I will not have a table with a lot of possible values equal to NULL, and the 'Availability' is a relation just for "Telephone Contacts"
How can I represent the relation between "Representative" and "Student" 1:N, understanding that both are an occurrence of a Entity table? something really similar happens with the relation between Director and DirectorType. I guess that your diagram support the relation between Director and DirectorType with the Ref_Entity_Types table, but what about the relation between Student and Representative, Do I just need to create a new field in Entity Table called Representative_ID (see picture 2), which could be null for any entities different to Student? Can you notice the diagram which shows the relation between Entity and Contact?
Is it a good option manage a Datetime field as PK in Availability Table?
Can you give me some inputs about the location's way that I designed?
When finally I begin to create the tables, is not a better option work with Auto-Increment PK Bigint for each table in my model?