Alpha Video Training
Results 1 to 11 of 11

Thread: Database design question

  1. #1
    "Certified" Alphaholic Ronald Anusiewicz's Avatar
    Real Name
    Ronald Anusiewicz
    Join Date
    Oct 2000
    Location
    Southampton, MA
    Posts
    1,586

    Default Database design question

    What is the best way to handle this.

    Does it make sense to have one database with many tables ands sets? For example:

    Table Big Job
    15 sets
    35 tables
    25 forms

    Or would it be better to have multiple smaller databases with fewer sets and tables/forms in each.

    However; all the tables are in a sense related and would be used by the tables and forms in the other databases.

    What would you do?

    Ron

  2. #2
    "Certified" Alphaholic
    Real Name
    William Hanigsberg
    Join Date
    Apr 2000
    Location
    Toronto, ON
    Posts
    4,018

    Default RE: Database design question

    Ron,

    "Does it make sense to have one database with many tables ands sets?"

    I wouldn't preach to anyone but that is what I do. Lots small, specific sets rather than a few massive ones.

    And the numbers you mention do not seem that large for a single database.

    Bill

  3. #3
    Mark Campidonica
    Guest

    Default RE: Database design question

    If you are going to inter-relate much of the information in the tables, then you want one large database. Otherwise you will be cross-referencing directories to connect all the little databases, which can cause problems if you want to move your database.

    If the data is independent of each other, then each data set should be in a separate database. I am using Alpha's definitions here.

    One of the major advantages of a relational database is that you can slice and dice your information as much as you want.

    Most people recommend have many specific SETs, however, to make debugging easier and lessen the chance of index corruption.

    Mark

  4. #4
    Member
    Real Name
    Blake Watson
    Join Date
    Jan 2003
    Posts
    961

    Default RE: Database design question

    Ron,

    As Einstein said, "Make it as simple as possible, but no simpler."

    In some cases, you might find managing smaller databases easier. In fact, until you mentioned it, I had neglected that possiblity, which might work for me. But, by contrast, the project I'm working on consists of nearly 200 tables and sets and nearly 200 forms with, I think, over 200 functions and scripts. And uncounted xdialogs.

    I think if I were going to use separate databases, I would probably opt for keeping them in the same directory, which may negate some of the advantages (maybe). But that way, if they weren't completely discrete (say two of the databases shared a table), adding the new table in would be fairly easy. (Plus, I've seen enough messages on the board here with people having trouble keeping their directory structures straight to make me not want to wrestle with it.)

    One disadvantage I can is that if you need to use a function in one database that resides in another, you have to, uh, attach a library. (That really only makes me nervous because I haven't done it.)

    An advantage might be that a smaller database is less prone to corruption, and it could certainly be faster to compact.

    You might keep it all as one database, and revisit the question after working with it for a while.

    ===Blake===

  5. #5
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default RE: Database design question

    Ron,
    I wouldn't hesitate to put that and much more in a single database.Your app is by no means large in terms of number of tables and sets.I have some apps ( databases ) with several hundred tables and sets, hundreds of forms and reports and at least as many global scripts or functions. Opening the database takes a little bit of time as would refreshing the control panel. As far as I know, the only time refreshing the control panel is really necessary is when using the adhoc browse addin.
    Good naming conventions help keep things organized. Constant backups keep things safe.The only reason I could think of for using multiple databases is if you were trying to market a package in modules. Even then, I'm not sure it's necessary.
    John

  6. #6
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,763

    Default RE: Database design question

    Blake,

    I wouldn't worry about adding libraries. I've been doing it for quite awhile with no problems.

    Now, in v5, I've gone to creating my own compiled .aex files which I use as appropriate with each application. I find these advantageous because there is a tendency when attaching other libraries to update the 'local' copy of the library but not the original. This happens because all the scripts in an attached library can be edited from the control panel. Since .aex files are compiled, they are not visible in the control panel and updating a 'local' copy is not an issue - updates must be done in the 'parent' application.

    On my own system, all of these .aex files are loaded in the A5v5\Addins_installed folder. The names are fairly self-explanatory: AIMS_StdFuncs.aex, AIMS_StdScripts.aex, AIMS_Security_Funcs.aex, AIMS_Backups.aex. Then, of course, there are my various developer addins that have no use for the normal user - AIMS_Developer_code.aex, AIMS_Script_format.aex, Delete_operations.aex, and AIMS_Group_Format.aex.

    By keeping these in .aex files separate from the regular global scripts, I can update one file and distribute it to everyone without any need to edit individual applications. I've lost count of how many times I've either fixed a problem or added a new feature or function/script to these files. I'm always careful to keep them backward compatible but, other than that very minor issue, it makes upgrades much easier.

  7. #7
    Thomas Henkel
    Guest

    Default RE: Database design question

    We have an enormous system where the need for separate databases actually exists. Our information, in most cases, is distinct per application, but we needed to present a common front-end. 35 sets is fairly small, and one database can handle that with no problem. The number of tables and forms you describe is certainly not a problem.

    Tom

  8. #8
    Member
    Real Name
    Blake Watson
    Join Date
    Jan 2003
    Posts
    961

    Default RE: Database design question

    I agree that it's probably not necessary.

    However, I do find the current app harder to get around in than a smaller app would be. There's a lot of extraneous scrolling. (All A5 apps are harder to get around in than I'd like, but the problem is compounded as the app gets larger.)

    So, what's a "big" database? :-)

  9. #9
    Member
    Real Name
    Blake Watson
    Join Date
    Jan 2003
    Posts
    961

    Default RE: Database design question

    I wouldn't worry about adding libraries. I've been doing it for quite awhile with no problems.

    That's reassuring.

    Now, in v5, I've gone to creating my own compiled .aex files which I use as appropriate with each application. I find these advantageous because there is a tendency when attaching other libraries to update the 'local' copy of the library but not the original. This happens because all the scripts in an attached library can be edited from the control panel. Since .aex files are compiled, they are not visible in the control panel and updating a 'local' copy is not an issue - updates must be done in the 'parent' application.

    Ye gods. So when you import a library, you actually get a copy? =That's= critical to know. When you update the original, do you have to reimport?

    Your solution sounds like a good one. Do you create empty databases and just add functions--is that how you set up your libraries?

  10. #10
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,763

    Default RE: Database design question

    So when you import a library, you actually get a copy?
    When you attach a .al* library from another database(this was possible in v4 also), that library is treated just like the scripts in the 'parent' database. They are visible and can be altered unless password protected.

    Do you create empty databases and just add functions--is that how you set up your libraries?
    Well, that was the intention. However, I've usually ended up adding some test tables so I could check out the results of my work.

    NOTE: Another really nice feature is the Import/Export options for scripts.
    1. This is a good script backup method.
    2. Sometimes the database ends up with little 'extras' that I don't want to be included in the .aex file so I just export all scripts and functions, delete the unwanted 'extras', create the .aex file, and import everything back in again.

  11. #11
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,763

    Default RE: Database design question

    When you update the original, do you have to reimport?
    That depends. If you have attached directly to the original, re-attaching is not necessary. (This type of library is not imported; it's attached.) However, I was using these as standard libraries - in fact, the v4 library was actually called Std_code.AL* - so they were usually copied to the customer's folder and attached from there so the customer's installation would still be able to locate the library because it was still in the application folder. The problems arose when I updated one application but didn't do it to others and later couldn't figure out which one was 'correct'.

    The initial changes may take slightly more time with the .aex files but the overall efficiency is much better.

Similar Threads

  1. Subscriptions Database Date Fields Design
    By Joe Fox in forum Alpha Five Version 5
    Replies: 0
    Last Post: 03-15-2004, 07:15 AM
  2. Modular Database Design Question
    By Peter.Wayne in forum Alpha Five Version 5
    Replies: 9
    Last Post: 09-07-2003, 08:29 PM
  3. Database design progressive update
    By Chris Teo in forum Alpha Five Version 5
    Replies: 2
    Last Post: 08-07-2002, 06:57 PM
  4. Database Design Questions...
    By John Dodson in forum Alpha Five Version 4
    Replies: 8
    Last Post: 02-26-2002, 02:19 PM
  5. Designers: Front end for database design
    By tlyon in forum Alpha Five Version 4
    Replies: 5
    Last Post: 06-11-2001, 12:08 PM

Bookmarks

Posting Permissions

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