Re: Database design pattern question

Andrew McLean wrote:
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.

One principle that you should always follow is to keep the design as simple
as possible (while still doing the job). One key reason is to minimize the cost and other impacts of maintenance over the life of the application. IBM ran some studies in the early 1960s which revealed that doubling complexity increased system life cycle cost by a factor of 4!

	[ snip ]


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.


Whoa, what you describe is mutually exclusive. Either you will do local processing and synchronize periodically or you will run a truly parallel database operation.

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..

If you only need to synchronize once every day or so this will work. 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. A more attractive alternative is to consolidate your transaction logs and then download the consolidated transaction log and run it against each of the local databases. This is preferable because it will only be necessary to download transaction activity for a day or two at a time rather than downloading the entire database.

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.


If the RDBMS you are using permits cross system joins and supports UNIONS
this too will work. The first step would be to create an updated central
base by doing a cross system join UNION operation that would build an updated event table. The second step would consist of downloading the event table to each of your local databases and then dropping the old event table and doing a create table as select from table.

My personal preference is for the first approach. I think it is safer to download transactions and run them against an existing database table.

HTH
Jerry
.

Relevant Pages

  • Re: Database design pattern question
    ... 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. ... 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.. ...
    (comp.databases)
  • Re: ASP.NET with SQL image type for up/down...
    ... the contents of a binary in/out of a database table. ... SQL Server MVP ... > I've written code web-based uploading and downloading. ...
    (microsoft.public.sqlserver.programming)
  • Re: reading a big file
    ... sense to put the growing file into a database and write reports out of ... the database that extract the needed data. ... > The problem is that if I just openthe file and verify each line to see if ... > line means downloading the whole content of the file on my computer, ...
    (perl.beginners)
  • Security settings for downloaded databases
    ... which means I'm constantly downloading databases ... from the Internet in order to grade and/or troubleshoot them: ... database files per week, minimum. ... database before I'm allowed to work with it. ...
    (comp.databases.ms-access)
  • File upload/download from database. Download appends aspx page to end of file
    ... I am trying to upload a file and save it in a Sql Server 2000 database. ... it appears that the page that is downloading this file is being appended to ... out attachmentFileName, out attachmentMimeType)) ...
    (microsoft.public.dotnet.framework.aspnet)