Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Database Design Thoughts, Help!

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Database Design Thoughts, Help!

    I am in the midst of a redesign of a database that has been running for 3 years now. One piece has always been problematic and I am sure there is a way to do this easily, but I am hoping that smarter folks, that's everyone else, could give me an idea.

    Table one: Parent for an Aircraft Reservation

    Child Table: Services for that aircraft reservation. Basically a Service type field and any notes

    There will be 15 or so hard coded service types and there can be many of the same services for each reservation. Many times only 2-3 services are used.

    I need to create a status board which lists all of the reservations and checks to see the lowest status for each type of service (15 possible) that exists amongst the child records for that reservation and display it in a table format. This could get quite computer labor intensive looking up each displayed reservation and all of the services for that reservation. My current application is a flat file containing all of the services possible in the reservation record, but this is very problematic and one of the reasons for the switch.

    The status board runs on three machines and is updated every 30 seconds which is why I would like to have the most minimally invasive way to get this data.

    Is there an easy calculated field perhaps that can look at only the child records and then I can check all 15 possible services to get the lowest value? This has been in my mind for the last three years and I want to move forward with the new rewrite. Thanks for your time in advance.

    #2
    Re: Database Design Thoughts, Help!

    How many parent records do you see as possible over time?
    The child records could be kept in one table separated by type( a look up table for consistency )
    Remember that child table records would be multiplied by parent times types used. That can get large.

    Possibly know some of the types of services could help others help you in a better manor??
    Dave Mason
    [email protected]
    Skype is dave.mason46

    Comment


      #3
      Re: Database Design Thoughts, Help!

      When you redesign, are you moving to a SQL database or an Alpha Database? (I recommend MYSQL or MSSQL)

      Either way, the best way to make this work correctly is with proper indexing.

      In the child table, you should only have services types linked to the parent table that exist. If the service isn't being used, then it doesn't need to be there. (You can add the service if they end up needing it.)

      That will eliminate a bunch of blank, unnecessary service records and speed up processing time.

      If it still ends up being too slow, you could create a third table which stores only the records that have the lowest value.

      If you are using SQL, you would setup a trigger to keep this third table populated. Then your dashboard would only refresh from the one table.

      Comment


        #4
        Re: Database Design Thoughts, Help!

        Guys thanks for taking a look at this. First I have never worked with sql. I have been a dbf guy since DOS

        Also I used to do a bunch of filtered indexes which really slowed up my system as there were so many for one table.

        DAVE: The idea is a reservation is made and the aircraft requests services such as fuel, oil, oxygen, newspapers, ice, catering, etc... The master record has both the arrival and departure date and time and the services would be entered first at initial call but then are updated during the day. The board always has to be current. I went with a flat file originally as it was easier to create the form which used an embedded browse (very complex one) to display all the data.

        I thought that by getting rid of the dead weight and only having each service needed as a child record, I could cut back on much dead weight. There are other things too. The original app has a reservation record as the parent and 2 children records, one arrival and one departure, each of those records had the services on them. There are also a bunch of one to one tables that make up the set to create the board. I also want to start charging for services which make sense to have individual records for each service, i.e. lnvoice line items.

        I thought there must be a relatively easy way to do this, but it is not as easy as it seems.
        Attached Files

        Comment


          #5
          Re: Database Design Thoughts, Help!

          For something this complex and with that many tables, you really want to use a SQL database - and putting it on a server with a fast hard drive wouldn't hurt either. :)

          A well designed SQL DB will handle this easily.

          Comment


            #6
            Re: Database Design Thoughts, Help!

            That is one busy form
            I try to stay totally away from filtered indexes for the reason you stated.
            Remember that multiple tables in a set show as a single table unless they are one to many and even then to a degree.
            No, I would not slow my working app down by using sql.
            Dave Mason
            [email protected]
            Skype is dave.mason46

            Comment


              #7
              Re: Database Design Thoughts, Help!

              Dave's opposite response to mine sort of begs the question ...

              What is wrong with your current app that you are redesigning it for? Is it getting too slow as currently designed? Are you running into some limits?

              What are the parameters that you are working with? (For instance, if SQL is totally out of the question, I won't bother mentioning it again.)

              Comment


                #8
                Re: Database Design Thoughts, Help!

                For instance, if SQL is totally out of the question, I won't bother mentioning it again
                One can use sql, it just slows the operation of the app a bit. No other benefit or detraction from app operation.

                I can see where he has a lot of fields with a lot of data and this does not have the appearance of a flat file type application.

                I would like to know how large the files are and how many records are in the database. I would also like to see the indexes. Those are a big factor to how an app needs to be built/modified.
                Dave Mason
                [email protected]
                Skype is dave.mason46

                Comment


                  #9
                  Re: Database Design Thoughts, Help!

                  Hi John,
                  I remember this app - think I still have an old copy of it even.

                  If the main area of your schedule is the large green rectangle, I would create a browse and embed it in an Xdialog and turn it into a modeless dockable panel so it stays open (assuming of course it can be done!). Then you can open up the other sections also in XD as you want to see more detail. That should keep the overhead down.
                  Robin

                  Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

                  Comment


                    #10
                    Re: Database Design Thoughts, Help!

                    SQL is probably out of the question for the time being. Yes, I am running into performance issues which I attribute some to an older 100 mb network to some degree. However the app runs on 6 workstations each updating every 30 seconds. I finally figured out how to stop the updating when the app was not in focus which seemed to help a bit. I have about 7 tables and a bunch of indexes to make that screen. One of the biggest issues, which I did not foresee at the time was I needed to historically look back at what services were captured on arrival, which could be days ago and not visible. By splitting up the departure/arrival into two records became an issue because I had to always check (via a lookup calculation) and get info from the arrival. There are several filtered indexes to make this happen. This really caused a lag. Also the browse has so many color formulas and display formats which seems to also be a huge part of the lag.

                    The flat file part of the data I meant was that I included each service item with a "status" field and 250 character comment for each 15 service items in one record. I need to break out those services for things such as billing ease and reporting. I thought from a design standpoint that it makes more sense to do it that way. Three years ago, I had to get a technological solution up quickly but did not know how to do what I am proposing now. I have time to redo this and thought that I should go back to my original idea of having each service item as a child record which seems to make sense.

                    My primary table reservations has 5000 records and about 1.7MB. I thought this was no big deal, but when I archive off a bunch of data the system is exponentially faster. The main child which has the services and the departure and arrival info is about 10,000 records (2 for each reservation) and is 11MB as it has all of those rarely used 250 character fields. There are also 9 indexes which each has a use, but I think this is a chink in the armor. The new proposed design should get rid of most of them. Actually after review, I cut down the fields last year to 100 characters to save space and I am harangued about that all the time because they want more space. The main screen is very busy but I wanted to create a system where all was easily accessed from one screen where other products on the market have 7 layers to drill down through to get to the info (which is why we created our own system. I hope that gives you enough info for thought. Thanks again.

                    Comment


                      #11
                      Re: Database Design Thoughts, Help!

                      Hi Robin,

                      Yes same app, new direction. I need to have a complete status board like I have now but instead of the the one record being displayed, as is now, I want to calculate it from child records. I have thought about posting the status to the master record(does not seem viable) or creating an onsave event every time the record is modified to "post" the status to the parent database (this seems doable but clumsy). It would be nice to somehow look at the children and create the status board on the records that are there.

                      I guess an easy way to get the max/min of the status field in the children being able to filter it by type and reservation number is the way to do it?

                      Comment


                        #12
                        Re: Database Design Thoughts, Help!

                        Generally(not always), when you can remove records and speed the system up a great deal. The indexes are the culprit.

                        The sizes of your tables should not be a problem at this point. Those are not large sizes. The access is where your problems are. From testing, breaking a table into parts and getting the notes/memo files into their own tables may be a good start. Properly designed table/set and properly used indexes could be a great benefit. There are better ways to filter records than resorting to indexes, although well thought out base indexes can greatly enhance things like queries, searches and thus filters.

                        I do like my apps fast and crisp no matter the size. I have dbf files over 500 meg in size(2 gig is the limit) and many thousand of records that perform well on a slower win 7 machine used as a server and a not so fast wireless system(that is how I test them). I also bought a couple of tools available to help me measure the speed and so keep me headed right.

                        A 1 gig network is really helpful and not that expensive anymore. The cards are probably the most expensive for you from what you said. Wired is better than wireless unless you have a really good wireless router(about 200.00).


                        Tom Cone gave me the place to go where I could download the tools here: http://msgboard.alphasoftware.com/al...es-for-desktop . They are all working well and doing the jobs as advertised and great support.

                        I still have not seen the indexes and would be super curious how they are written???
                        Dave Mason
                        [email protected]
                        Skype is dave.mason46

                        Comment


                          #13
                          Re: Database Design Thoughts, Help!

                          I just tested a script that runs a function(several) for appends. The function checked appends 337000 records and takes 50.426 seconds. If I run it from the laptop, it will take longer.
                          Tested using CSDA code utility. This is a large table in my estimation at 120 fields. I have 5 indexes that are all very simple and a number of basic field rules. This table is never a stand alone, but a one to many child table. It was a 400 field table and I broke it into 4 parts for speed and size because it was at 1.3 gigabytes already. If I added all the part up, it would take about 3.5 minutes to append all 4 tables with 337,000 records each.


                          CSDA is great tool for me.
                          Dave Mason
                          [email protected]
                          Skype is dave.mason46

                          Comment


                            #14
                            Re: Database Design Thoughts, Help!

                            John,
                            Since this a daily schedule, how many arrivals and departures would there be in a given day?

                            And for how many days would this schedule have to span until all charges are incurred?

                            I am assuming that once a plane is departed, it needn't be on the schedule any more, but might still need to be tracked for billing?

                            Is this a 24/7 operation,and if so when does a new day begin?

                            My thoughts concern the use of temp tables...
                            Last edited by MoGrace; 11-07-2014, 03:55 PM.
                            Robin

                            Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

                            Comment


                              #15
                              Re: Database Design Thoughts, Help!

                              Robin may be on a good track here. Do not know why I did not think of it. I have done this in the past where a company needed 3 years data to be real available and had to keep old records for 10 years that could be searched if needed. Their system is set up to append the complete records to the permanent archive app and then delete the appended files after 1100 days from final completion of the deal(car dealer).

                              You could have the whole thing set up as you do now, but with only(say) 1 weeks worth of data(or 1 month). After the week/month/year/47 days, the data is uniquely appended(copied) to a complete other set of applications files and deleted from the current working files. This may be a great way of minimizing the work involved in a re-write or heavy changes.
                              The other set of files or app could always be called on demand.

                              Being more through, a deletion of all files more than 7 days old(or whatever) would occur after they had been appended to the other app files.
                              Dave Mason
                              [email protected]
                              Skype is dave.mason46

                              Comment

                              Working...
                              X