Hey,

I have a preexisting database with tables Teacher, Principal, AssistantPrincipal. I want to create a WebLogin Entity (WebLoginId, Username, Password) that shares a one to one relationship with each of the tables above. For every teacher, there is exactly one corresponding WebLogin. For every WebLogin, there is exactly one corresponding teacher.

My first thought is to create a relation such as WebLogin(WebLoginId, Username, Password, TeacherId, PrincipalId, AssistantPrincipalId).
The TeacherId, PrincipalId, and AssistantPrincipalId would be foreign keys into their respective tables. However, in this method, two of the employee IDs are always left null.

Is there a more elegant solution? I'm quite new at this.

Thanks!

Given your existing data model, there probably isn't. Your solution is quite a standard one anyway - with a CHECK constraint to ensure that one and only one of TeacherId, PrincipalId, and AssistantPrincipalId is populated on each row.

You could turn it around and hold a WebLoginId in the other 3 tables, but then it would be hard (not impossible) to ensure that a Teacher and a Principal (say) don't share the same login details.

Maybe Teachers, Principals and Assistant Principals should all have been in one table in the first place, but it's too late to worry about that now.

Thank you for the response Tony.

Is it a better practice to split the table into several smaller linking tables to eliminate the nulls?

WebLogin(WebLoginId, Username, Password)
WebLoginTeacher(WebLoginId, TeacherId)
WebLoginPrincipal(WebLoginId, PrincipalId)

I don't think so, no. It has the same issues as putting foreign keys to WebLogin in the other 3 tables, i.e. hard to ensure a Teacher and a Principal don't share the same WebLogin. Plus it involves extra tables.

Looks like a supertype / subtype example to me:

WebLogin (TeacherId, WebLoginId, Username, Password)
KEY (TeacherId)
KEY (WebLoginId)

Teacher (TeacherId) KEY (TeacherId)
Principal (TeacherId) KEY (TeacherId)
AssistantPrincipal (TeacherId) KEY (TeacherId)


Teacher is the parent table being referenced by all the others.