Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    1

    Question Unanswered: Relationships vs. field count

    Can anybody comment on which is better (faster over a network) - a large database with many fields, or a database with relationships to many smaller files?

    Is a database with 12,000 records, each with 150 fields better or worse than a database with 12,000 records with 20 fields linked to 10 other databases with 2 to 12000 records and 5 to 20 fields each? In most cases the related file will have one record for every record in the main database. Keeping it related minimizes the number fields in the main database, but probably adds complexity due to the relationship.

    Does it matter? Will reports calculate out of the flatter database faster? Is one more stable?

  2. #2
    Join Date
    Sep 2003
    Location
    So. Cal. USA
    Posts
    142

    Re: Relationships vs. field count

    Originally posted by Russell Gnant
    Can anybody comment on which is better (faster over a network) - a large database with many fields, or a database with relationships to many smaller files?

    Is a database with 12,000 records, each with 150 fields better or worse than a database with 12,000 records with 20 fields linked to 10 other databases with 2 to 12000 records and 5 to 20 fields each? In most cases the related file will have one record for every record in the main database. Keeping it related minimizes the number fields in the main database, but probably adds complexity due to the relationship.

    Does it matter? Will reports calculate out of the flatter database faster? Is one more stable?
    Hi, RG! I don't have experience w/big db's, but FWIW, I think you should go with the best over all plan and not worry about speed issues wrt to your design. FileMaker is fast. Network traffic (email, uploads/downloads, web browsing, file sharing, print jobs, etc.) will probably affect your net speed (pun!) more than the design.

    I do not use FileMaker Server, but you should probably look at this product if you haven't already as it will probably improve speed and stability a great deal. I hear the next version of FileMaker is coming out soon (very soon) and it will incorporate the use of a single database with multiple tables as other systems do. The current method of related db's entails individual db's (1 db = 1 table) for which you can define relations. Normally, this doesn't matter too much but if you have several complex db systems going, you may have a hard time distinguishing between their component db's... They'll all be open and running, but it may hard to tell which "customers" db belongs to which system, for example.

    Again, this is not my forte but I think it's worth considering.
    --ST

  3. #3
    Join Date
    Feb 2004
    Posts
    84

    Re: Relationships vs. field count

    Originally posted by Russell Gnant
    Can anybody comment on which is better (faster over a network) - a large database with many fields, or a database with relationships to many smaller files?

    Is a database with 12,000 records, each with 150 fields better or worse than a database with 12,000 records with 20 fields linked to 10 other databases with 2 to 12000 records and 5 to 20 fields each? In most cases the related file will have one record for every record in the main database. Keeping it related minimizes the number fields in the main database, but probably adds complexity due to the relationship.

    Does it matter? Will reports calculate out of the flatter database faster? Is one more stable?

    Yes, no and maybe.... The message from the previous poster is kind to FileMaker. FileMaker Pro has used the same basic code since FileMaker 2! FileMaker is very slow compared to most other database environemts. FileMaker has total re-written FMP 7 and it indeed is fast my today standards, but the speed of FileMaker's back-end is not the primary issue here.

    I typically do not have an issue with lot of fields if they are all needed. If all your relationships are 1 to 1, and they every primary record always has a child record, then I see no need to break the data up just for the fun of it.

    The speed issue generally has more to do with which fields are indexed. It is possible to have a key field that is not indexed and this can make the database slow.

    My general advise to create 'well-formed' databases as defined through the 'normalization process'. First Order Normal- Second Order Normal - Thrid Order Normal. You should be able to find plenty of info on 'normal' orders in database design. I know that UC Berkely has a bunch of web page online delaing with this. When your database slows down, look at which fields are indexed. Yoy may also want to consider which table the report is called from (I can make major differances in speed)


    thanks,


    Joe

Posting Permissions

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