Re: Database design pattern question

In article <SRNCe.4869$Kz3.3058@trndny04>, jerry gitomer <jgitomer@xxxxxxxxxxx> writes
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.

I'll put that another way, I didn't understand how that related to my situation.

[snip]

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.


OK. That sounds sensible. Its similar to what I had in mind except you are suggesting having one table for all the attributes, rather than one for each. I can see that that is an improvement.

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. 

Fair enough.

I'm finding the discussion useful, thanks to everyone who has contributed. The one issue no one has commented on so far is actually my major motivation for the suggestion I made. That is the problem that I really need to be able to have multiple copies of the databases being updated in parallel and to have the ability to synchronise them later.

I see two approaches to this:

1. Maintain a transaction log and "replay" the transaction log for each copy of the database into the other databases..

2. My idea of deliberately storing the data that is being updated in the form of events, such that synchronisation simply becomes a matter of forming the union of the multiple copies of the event table.

Any comments?

--
Andrew McLean
.

Relevant Pages

  • Re: Is msdb transactional???
    ... By default the msdb database is not transactional. ... If you do this then you might need to backup the transaction ... perform transaction log backups. ... All data is eventually stored in the data portion of the database. ...
    (microsoft.public.sqlserver.server)
  • Re: dataadapter.Update() multiple table update strategies
    ... not much on HOW to submit a massive update to a transaction sproc. ... block with all the needed SQL. ... Am I supposed to not do SQL joins, but instead pull my whole database ... Or should I create multiple data adapters for each SQL table UPDATE I need ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Removing un necessary log
    ... If the database is in Full Recovery mode, ... If you have long running transactions, that will prevent the transaction log ... database is running and production database. ...
    (microsoft.public.sqlserver.server)
  • Re: Newbie Question on DB Maintenance Plan
    ... database maintenance plan you specify a database backup, ... you back up the transaction log independetly from the database. ...
    (comp.databases.ms-sqlserver)
  • Re: Database design pattern question
    ... That is the problem that I really need to be able to have multiple copies of the databases being updated in parallel and to have the ability to synchronise them later. ... Maintain a transaction log and "replay" the transaction log for each copy of the database into the other databases.. ... If you go this route consider updating a central database as the first step and then downloading the central database to each of your local databases. ...
    (comp.databases)