Single table vs Two views...

[madEG]

Hello Gang, I was hoping someone could help me think this through.

I have an access db that is being heavily modified, and I was trying to make sure that the architecture was being done right at the start of the mods...

The current situation is this: Two of the tables in the current db store people info: one table is tblTeachers and the other is tblContacts. Their info/fields are pretty different as different information is being stored for each of these two "people" tables...

But, the tblTeachers info falls out of date as it changes pretty often - and I found that the masters teachers info is being maintained in another sql server application - so I would like to use that data...

My first thought was to have the two tables combined into a single table (tblpersons) with their person type fkey setting who the person is, and what info they maintain. But that gives a rise to the problem of the application having write access to the Contacts info - but we need to maintain a readonly view of the Teachers info as the data steward for the teachers app is the only one to make changes to the teahcer's data.

So I was thinking that the new system is to have tblTeachers info being maintained in that other application with that info being seen in the access db through a readonly SQL view from sql server. (linked via odbc)

...Ok, so I guess my question is: does it make sense to have two tables in access db that looks to two different views (one to tblPersons with a view that only shows Contact's info) and another to the same TblPeople table (but with a readonly view that only shows/subsets the teachers subset of people)?

Does this make sense?

Maybe I should just have a r/o view of the teachers from the maintained database and then have the contacts info stored locally at the access db keeping contacts off the main people table, as this system's "contacts" are not really of interest to the rest of the folks who use the master tblPeople table in the sql server db.

Thoughts? I hope this is reasonably clear...

Thanks,
-Matt G.

 

[The_Doc_Man]

As long as you have the read/only or read/write attributes and filters set up correctly, this absolutely makes sense.

 

[madEG]

Ok, so yea...

I am gonna go with two tables in the access front end - one that looks via a read-only view (to teachers) to sql server... and the other is set to a read/write view to a table collecting the contacts' info.

Ok - maybe I just had to hear myself write it out and read it aloud - heh

Thanks Doc!

 

[The_Doc_Man]

No problem. I work in a government shop where individual access to data varies by the roles that everyone plays, so we have lots of cases where views are designed for this group or that group, each one with different update-allowed portions. Very commonplace.

The hardest part is when you have a bunch of those that hit the same set of tables but with different update ability or selective view, is keeping straight which one is which.