1

Good day, My problem is how to model databases. I most times get confused, not knowing what table to create or what will be needed.

Is there like a standard someone can take to get things working?

My current project is a simple University Staff management application, to help the School Admin:

  1. See the distribution of staff arranged by rank in every unit of the
  2. university(programme, dept and faculty).
  3. Carry out transfer of staff
  4. Carry out promotion of staff
  5. Carry out removal of staff
  6. Carry out addition of staff
  7. Answer simple queries, e.g, display professors in faculty of Arts by dept, etc.

1 Answer 1

4

I think the way I would tackle a problem like this is to examine it through three lenses (although more may be appropriate in your case).

  1. Objects (usually the nouns in your problem statement)
  2. Relationships (how the nouns interact)
  3. Actions (usually the verbs in your problem statement)

Before I get too much further into this - if you don't mind duplicate data (or potential data-entry errors in your database) then this could be created as a simple flat table with each of the items like programme, department, and facility being text fields associated with each staff member. This, however as I mention, is prone to data entry errors although it makes updating data far simpler.

I am going to propose having several tables which provide you with a hierarchical view of the data. In this case, I'll try to walk through each and explain how I would tackle it:

Objects

I see a few objects that you might want to incorporate in your database:

  1. Staff
  2. Programme
  3. Department
  4. Facility
  5. Position / Role

Each of these will very likely become individual tables in your database. It is best practice to identify these objects with a primary key and a unique identifier (often times an incremental Id column in each table. This, then, allows you to take the next step ...

Relationships

Next, you identify what the relationships are between the objects you've defined. For example, a position / role may "belong to" (be associated with) one department or programme - depending upon how you define those (e.g., an Assistant Professor of Database Design might belong to the Software Department which belongs to the Management Information Sciences programme or vice-versa). A staff member would generally hold only one position / role. If this were the case you would have a tree like this: Staff -> Position -> Department -> Programme (or Staff -> Position -> Programme -> Department depending upon what each means).

If the first is the case, you would likely have a Staff table with a foreign key column called PositionId (or something similar). This PositionId would link in a one-to-many relationship from the Position table to the Staff table. The same would apply for the Position table, which would have a foreign key column called DepartmentId (or ProgrammeId) that had a similar relationship link to the Position table.

One kink in this logic is that it is sometimes possible for a staff person to hold more than one position. This would require a many-to-many relationship which requires a junction table in the middle. For example, if a staff person could hold multiple positions then you would create a new junction table called something like StaffPosition which would have a StaffId and PositionId column in it. This table, then would have one-to-many relationships between it and Staff (on StaffId) and Position (on PositionId). This would make updating the data more complicated, but walking through that is slightly out of the scope of this response.

Actions

Finally, the actions that you define would impact how you are going to interact with these tables. Going through each of your items, I'll give you my best guess on what those would look like (assuming that a Staff person can only hold one position).

  1. Not an action
  2. Not an action
  3. Carry out transfer of staff - perform an UPDATE on the Staff table that changes the PositionId to the new position.
  4. Carry out promotion of staff - perform an UPDATE on the Staff table that changes the PositionId to the new position.
  5. Carry out removal of staff - in this case you might want to do two things: (1) Add a boolean / bit field to the Staff table that indicates whether the staff person is currently employed - you would turn that to OFF in this case and (2) set the PositionId to NULL. You could also, of course, delete the row in the Staff table, but you may need to access this data later (or they may be rehired).
  6. Carry out addition of staff - this would be a simple INSERT into the Staff table, assigning the appropriate PositionId as applicable.
  7. Answer simple queries - the basic schema I've proposed would allow that through some simple INNER JOINS on the proposed tables.

One thing I would mention is that in this configuration, you will need to have additional administrative functions to allow users to not only add Staff persons, but also add / delete Positions, Programmes, Departments, and Facilities (or you can use SQL to do that yourself).

Conclusion This is a very simple overview and I could see the data model getting pretty complex in your situation. But hopefully this will give you and idea of where to start and how to do so.

4
  • wow... this is great. Still reading tho... was thinking of adding students. Aug 25, 2018 at 13:28
  • A Student table would not be much different from a Staff table - go through the same steps (identify the objects, relationships, and actions). Without setting up a consulting gig, I'm not sure how much more help I can be in this format. Aug 27, 2018 at 11:14
  • okay thanks. Then in the aspect of doing login, i.e one login for both admin, lecturer and student what do you suggest? I was thinking of having a user table but then when admin is creating a user(lecturer/student) the insert statement will have to insert into multiple tables lets say 2 tables Aug 28, 2018 at 11:53
  • Logins can generally be completely disconnected from the objects that might represent them in the database. You may want to create an admin user who can perform all functions in the database, whereas a department head or administrator may only be able to add/remove students from classes in their department, and a student may only be able to perform SELECT statements on their classes. If you need to associate a "user" with a student/professor/etc. you might then want to create some many-to-many tables (e.g., User -> UserStudent <- Student, etc.) to keep those associations. Aug 29, 2018 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.