G
Guy Horton
The Scenario
A University tracks many categories of people - student, lecturer, prospect, director, admin, and so on, as well as their address and contact information.
I would like to model the information as a Supertype and Subtype relationship. Everyone is a person (the supertype entity). Then, an individual can be any of the above special types of person (lecturer, student...). All the categories of persons will have a 1 to 1 relationship with person, and share either the same primary key or be related to person via a foreign key, although, a person may be a student and a lecturer, or any other combination of person categories.
A person may have as many addresses or contacts as needed, although for the purposes of this scenario we can ignore the person address and contact information. The following vary incomplete schema will hopefully assist in giving you a feel for the design.
tblPerson
PersonId PK
Salutation
FirstName
LastName
PersonNotes
....
tblStudent (1:1 relationship to tblPerson)
PersonId PK
BirthDate
Citizenship
Sex
DateEnrolled
....
tblDirector (1:1 relationship to tblPerson)
DirectorId PK
PersonId FK
Department
CampusLocation
RoomLocation
....
More Information
The system already exists (not a University System), but it doesn't implement the person/person categories as supertype and subtype relationships, but as a large table with many data redundancies.
All the forms are bound Access 2002 .adp forms using SQL Server 2000. However, following a physical design feasibility review it does not appear easily, or seamlessly possible, via a view or stored procedure, even with a resync command, to Insert new records in a 1:1 relationship into two different tables at the same time on the same form, especially, when the supertype entity - tblPerson has an identity primary key.
(I receive an error similar to that described in Microsoft KB article Q295250, however, defining a relationship between the tables doesn't resolve the problem. Although, no problems are experienced modifying existing data either side, or both sides of the relationship. I have also reviewed the actions taken against the database using the SQL Profiler and believe I have an understanding as to why it isn't working, but the above does work for an .mdb based solution.)
As I would like to avoid, re-writing as much of the existing system as possible, (ie. creating subforms to manage the insert of the subtype records). I would like to ask if the above problem:
1) Is an error or desired behavior? (it doesn't surprise me that the Access OLEDB SQL Server interface doesn't support all the functionality of the Access to .mdb interface)
2) Thoughts on any alternative approaches either in terms of the database design, or form and/or query design.
Your Assistance Appreciated
Guy
A University tracks many categories of people - student, lecturer, prospect, director, admin, and so on, as well as their address and contact information.
I would like to model the information as a Supertype and Subtype relationship. Everyone is a person (the supertype entity). Then, an individual can be any of the above special types of person (lecturer, student...). All the categories of persons will have a 1 to 1 relationship with person, and share either the same primary key or be related to person via a foreign key, although, a person may be a student and a lecturer, or any other combination of person categories.
A person may have as many addresses or contacts as needed, although for the purposes of this scenario we can ignore the person address and contact information. The following vary incomplete schema will hopefully assist in giving you a feel for the design.
tblPerson
PersonId PK
Salutation
FirstName
LastName
PersonNotes
....
tblStudent (1:1 relationship to tblPerson)
PersonId PK
BirthDate
Citizenship
Sex
DateEnrolled
....
tblDirector (1:1 relationship to tblPerson)
DirectorId PK
PersonId FK
Department
CampusLocation
RoomLocation
....
More Information
The system already exists (not a University System), but it doesn't implement the person/person categories as supertype and subtype relationships, but as a large table with many data redundancies.
All the forms are bound Access 2002 .adp forms using SQL Server 2000. However, following a physical design feasibility review it does not appear easily, or seamlessly possible, via a view or stored procedure, even with a resync command, to Insert new records in a 1:1 relationship into two different tables at the same time on the same form, especially, when the supertype entity - tblPerson has an identity primary key.
(I receive an error similar to that described in Microsoft KB article Q295250, however, defining a relationship between the tables doesn't resolve the problem. Although, no problems are experienced modifying existing data either side, or both sides of the relationship. I have also reviewed the actions taken against the database using the SQL Profiler and believe I have an understanding as to why it isn't working, but the above does work for an .mdb based solution.)
As I would like to avoid, re-writing as much of the existing system as possible, (ie. creating subforms to manage the insert of the subtype records). I would like to ask if the above problem:
1) Is an error or desired behavior? (it doesn't surprise me that the Access OLEDB SQL Server interface doesn't support all the functionality of the Access to .mdb interface)
2) Thoughts on any alternative approaches either in terms of the database design, or form and/or query design.
Your Assistance Appreciated
Guy