Design for historical data

From: ER Slansky (anonymous_at_devdex.com)
Date: 10/29/04 

Date: Thu, 28 Oct 2004 18:32:10 -0700

Because my only real design experience is with Access, I don't know how
powerful SQL Server (2k) can be and the best way to design for it.

Moving from Access because table of historical data is simply getting
too large. I will have to keep history on an individual potentially for
years (for doctors office) because the same doctor may not be the one to
see an individual each time they come in. All prior history must be
called up to review when making a diagnosis.

There are also three types of visits with really nothing in common
except the patient, but the history of all three types of visits at
times must be displayed and other times only the history of a specific
type of visit displayed.

So, and I apologize if this is a trivial question for experienced
designers, I see two options:

1) Combine everything in one table with indexes on visit type and
patient - this would result in largest number of records in one table
with the fields that are dissimilar containing nulls.

2) Three separate tables for each type of visit containing only the
specific fields for that visit type and index off patient. To get a
complete history, I would have to search all three tables for the
patient, but when only needing history on one visit type, I would only
have to go to that particular table.

In Access, the design is currently one large table and to search, even
with an index on patient, is taking longer as the number of records
grows. I know SQL Server can hangle it much better, but would it handle
either option equally well? There are quite a few adds, and
modifications to the "current" visit record for maybe 2 or 3 days after
a visit, but really hardly ever any deletes.

How many records is "too many" for SQL - or is there such a thing??

Thanks,
ER Slansky
San Antonio, TX

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Design for historical data
    ... > types of visits are really that, 3 separate types of visits, which store ... billion rows of page delivery history in a system I helped build (I know ... Good design is important, decent, well balanced hardware (disk, ... Pro SQL Server 2000 Database Design - ...
    (microsoft.public.sqlserver.programming)
  • Re: Design for historical data
    ... types of visits are really that, 3 separate types of visits, which store ... is going to be poor table design and poor indexing. ... All prior history must be ... > patient - this would result in largest number of records in one table ...
    (microsoft.public.sqlserver.programming)
  • Re: Design for historical data
    ... SQL Server scales far larger than anything you'll see in your application. ... If you look at database size, the largest SQL Server database shown for DSS ... All prior history must be ... >> specific fields for that visit type and index off patient. ...
    (microsoft.public.sqlserver.programming)
  • Re: Data modeling in MV, revisited
    ... Since we can think of files as modeling ... persistence for some reason and popping it back up when using it, ... The history is its own relation. ... how developers are supposed to design the data model in each. ...
    (comp.databases.pick)
  • pt. 1A 25-Year-Old Woman With Hallucinations, Hypersexuality, Nightmares, and a
    ... A 25-Year-Old Woman With Hallucinations, Hypersexuality, Nightmares, ... History of the Present Illness ... paranoid schizophrenia since age 19, ... In the emergency room the patient suffered ...
    (sci.med.diseases.lyme)