Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    35

    Unanswered: What do you recommend?

    I have 5 years worth of data in 5 different databases, but their structure is identical. So the only difference in them is the data in what I have named the Main Table. There are approximatly 400,000 records in each of the main tables.

    I have created smaller tables that are linked to the Main Table so that I can run queries.

    The problem is, that if I update one of the small tables, I have to update the other 4.

    Should I just create another database with these small tables and link them to the other databases. I know the simple answer would be yes. but because of all the data, would it make the queries run slower??? Or any other suggestions.

    Thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109

    Re: What do you recommend?

    Originally posted by parra
    The problem is, that if I update one of the small tables, I have to update the other 4.
    What do you mean by UPDATE? Are you talking about the table structure? You know, if your datas are separated by year, you could just add a field named fld_year and put all of your datas in the same table, that way it would be simple. With proper index, it should NOT slow too much the application

    Edit: Ooop, sorry, added the part in bold

    JefB
    Last edited by JefB; 10-09-02 at 14:30.

  3. #3
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    I would do as you are suggesting - have a database for running the queries which contain the small tables. (Would these have to repeated 5 times if they were in the same database?) I would then leave the main tables in their separate databases and link them into the query database.
    If you index the main tables correctly, there should not be any difference in performance as running the separate databases.
    Roger Hampson
    XI - ecs (UK) Ltd

  4. #4
    Join Date
    Jul 2002
    Posts
    35
    Xiecsuk, I made a duplicate of the databases and linked the small tables to the Main Database but was unable to create a relationship to enforce referential integrity. Please read the other post I have on this same page. So what are my options now????

  5. #5
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    The other responses are right - you can't enforce referential integrity over linked tables.

    Can you give me some idea of what you are trying to do. What are the small tables used for and what sort of data are you changing. I had assumed that the main tables were only for running queries and that their data did not change. Is this not correct. Can you also answer JefB's question.
    Roger Hampson
    XI - ecs (UK) Ltd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •