Re: Database design pattern question

Andrew McLean wrote:
In article <1121628648.995189.91800@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>, --CELKO-- <jcelko212@xxxxxxxxxxxxx> writes

This design fallacy is called attribute splitting.  You are putting
things that belong to one entity split over multiple tables.



I'm afraid I don't understand this point.

Don't worry about understanding it for now. (If you choose to do more research on data bases you will understand it.) Just accept the fact that Joe knows what he is talking about and unless you have a good reason to do otherwise it is best to take his advice.

Temporal things are stored with a (start, end) timestamp pair.  They
have durations and are not points in time.  You can get the details at
University of Arizona's website -- look up Rick Snodgrass and download
his book.



The book looks very interesting. Thank you.

I suppose it might help if I explain a bit more about my application. Some of the data comes from different sources, with different levels of reliability. So for instance my event tables might store information about the source of the data as well as the time stamp and the data itself. I can also imagine wanting queries which use data from the most reliable source rather than the most recent.

A better example attribute might be surveying a person's favourite colour at different times. Really all you have is a snapshot in time. There isn't really an interval of validity resulting from the survey. Although once I have read Snodgrass I might get a feel for whether it is helpful to use an interval for which the information is most current.

The disadvantage of this approach would be that I could no longer simply merge multiple copies of the database by forming unions of the event tables.

I should really emphasise that the ability to update multiple copies of the database then merge them is the key issue I am trying to solve. Keeping a transaction log may be a better solution than what I was proposing, but I have a prejudice against having the same information recorded in multiple places.

Andrew,
The fact that you can have multiple occurrences of an attribute tells us that you have a one-to-many situation and not a simple one-to-one situation.

That being the case you should have two tables; one for persons and a second for attributes. The attributes table would contain columns for person_id, attribute id, value, date and reliability.

Your basic queries would then consist of a subquery against the attributes table to select the latest or the most reliable with the result being joined with the persons table.

A transaction file is, IMHO, mandatory. If your RDBMS doesn't maintain one you should. The reason is that any disk drive will, sooner or later, suffer a catastrophic failure. When it does it will be necessary to replace the disk drive and then rebuild your database using your most recent backup and a transaction file containing the transactions that have been processed since the backup was made.

The intent of the transaction file is to assist in disaster recovery and, if you wish, to provide an audit trail. It is not intended to be used for ordinary data processing.

HTH
Jerry
.

Relevant Pages

  • Re: Question about remote objects
    ... database service with connection to database ... client manager to service logins from client workstations ... the server. ... a row read in from the database, and with multiple users running multiple ...
    (comp.lang.python)
  • Re: Multiple databases - best performance scenario
    ... My company has the need to manage data for multiple customers. ... Multiple small physical servers connected to SAN ... When I'm using the word database ... You might consider checking licensing costs at the oracle store ...
    (comp.databases.oracle.server)
  • Re: Rails app organization question
    ... I -really- like Rails, though. ... Have one instance of the app and somehow allow it to use different databases depending on the user's login. ... Have multiple instances of the app running on virtual servers, similar to how cPanel sets up accounts for multiple hosts -- each host has its own directory, MySQL database, and so on. ...
    (comp.lang.ruby)
  • Re: Merging Data Dynamically
    ... not mind multiple back-ends. ... But if you had told me your Sailing Clubs are all owned by ... then loop through all the tables to detach them from 1 back end database ... sudden addition of Removals5 and create a linked table for it. ...
    (comp.databases.ms-access)
  • Re: Conversion to 2007
    ... All I know Iis have multiple tables used to store data and multiple tables ... Sometime I used multiple queries to filter the information or shape reports ... Office Pro 2007 and hope it will work as well with office 2003 ... so a well-designed database may work without modification in A2007. ...
    (microsoft.public.access.tablesdbdesign)