Database design pattern question

I'm new to database design, I've done some reading and think I have come up with the most natural database schema for my application, but I'm not sure if the approach I'm taking is practical. I would appreciate some advice before ploughing ahead.

I think I have come up with what might be thought of as a design pattern, which I haven't seen before. I may just have looked in the wrong places or it might be a bad idea.

I'll illustrate by very simple hypothetical example with 3 tables.

Person(PersonID, Name)
SexEvent(Timestamp, PersonID, Sex)
TitleEvent(Timestamp, PersonID, Title)

The idea is that we have a table of people, but we learn information about them, like their sex and title, at later dates when we interact with them. Rather than having columns in the Person table for these attributes, we store tables of "events", one column of which is a timestamp, when we learn this information.

I am considering this approach for two reasons, the first is to maintain an audit trail. But the main reason is it would be extremely helpful for my (unusual) application to be able to run multiple *non-networked* copies of the database. I think that by storing data in these event lists, subsequent synchronisation will be as simple as forming the union of multiple copies of the relevant event tables.

What I'm not sure about is the efficiency of this approach. The data is all there, but can we efficiently perform the required queries?

Take the following task. "List all the people and the most up to date information we have on their sex and title". Is it possible to construct an SQL query which returns such a table of (Name, Sex, Title)? Or do I have to loop over each person in the Person Table and construct queries to extract their Sex and Titles one at a time?

Any suggestions gratefully received.

--
Andrew McLean
.

Relevant Pages

  • Re: Very slow query
    ... Meanwhile, as my database is in production for some months now, how will I ... the Main table called Sex ... reports fall into the user interface category; the focus here is on making ...
    (microsoft.public.access.queries)
  • Re: Relationships. Does anyone use them?
    ... If you do not care to "go in into the relationships window to set the relationship" you are, quite simply, going to fail badly any relational database course. ... Relationships are the constraints that are needed to be set up to ensure database structural integrity. ... You really need to go over this in detail, because right now I can tell you, no offence intended and with the greatest of respect, that you are NOT DOING RELATIONAL DATABASE DESIGN WORK at present. ...
    (comp.databases.ms-access)
  • Re: Table design - reducing number of entities
    ... I've looked at a lot of crappy code written against databases. ... lot of that code is crappy, because the database design itself was crappy. ... > look at all the constraints on the putative entities--not forgetting the ...
    (comp.databases.theory)
  • Re: Relationships. Does anyone use them?
    ... why do I need to add 2 tables in the Relationship window and set relationships? ... If you do not care to "go in into the relationships window to set the relationship" you are, quite simply, going to fail badly any relational database course. ... When a database application gets large and widely used, the following is an example of what happens that simply screams for the lowest level constraints to be used. ... You really need to go over this in detail, because right now I can tell you, no offence intended and with the greatest of respect, that you are NOT DOING RELATIONAL DATABASE DESIGN WORK at present. ...
    (comp.databases.ms-access)
  • Re: Basic Database Design
    ... the Client has a Insurance Company ... you'll need some further knowledge on the subject of database design. ... Books: General: Beginner ...
    (microsoft.public.access.tablesdbdesign)