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

  • DaveM
    replied
    Re: Database Design Thoughts, Help!

    If separate tables for income and outgo, then a header with connections to both are in order.

    I have not seen your structure, But when you start talking about using indexes to control something, there is a problem somewhere.

    Leave a comment:


  • MoGrace
    replied
    Re: Database Design Thoughts, Help!

    On your screenshot, you show what looks like little list boxes for services that are empty - are these where a particular record selected will show what services have been done?

    Leave a comment:


  • johngtatp
    replied
    Re: Database Design Thoughts, Help!

    I thought about posting but I need to check the value of the target db, because there may be more than one service and I have to post the lowest status. I am using 1-4 for the status with 4 being complete. I want the status to indicate the worst case for that item. There may be multiples of the same service and I do not want to mark that service complete and overwrite one of the same types of service that has not been done. For example someone calls and says add a QT of oil to the left engine then call up later and request another QT for the same engine. Another rub which I am trying to figure out is that some services can happen both on arrival and departure, i.e. someone will want fuel on departure but not on arrival. So I need to add a selector on the Service as to when, however, then I need a way to "scan" the child records for the reservation and pull out the status's for each service AND which services for arrival and departure.

    I have used Max and Min and count for child records before and those methods are quick. If there is a way to do things like max or min on filtered child records, this would seem fairly quick as there are relatively few child records per reservation and the set has already identified the linked children.

    Leave a comment:


  • MoGrace
    replied
    Re: Database Design Thoughts, Help!

    Hi John,
    I am not sure what you mean by live summarize a status field, but wouldn't a post field rule in the various tasks you have do it?

    Leave a comment:


  • johngtatp
    replied
    Re: Database Design Thoughts, Help!

    These are interesting thoughts and I would have to think about how to implement this because I need a years worth of active history. for a years worth of data I have about 5000 reservations and 10,000 arrival and departures. One of my primary reasons for wanting to take a different approach is to put both the arrival and departure on on record. Much of my index creation problems stem from the fact that I need to compare the arrival and departure to see if services were performed when they got here or when they left and also to determine the time between them to indicate that to personnel that services are not done twice (which has happened before). I went through so many ways to try to easily do this, but I ended on filtered indexes which I think cause me latency issues as well as corrupt indexes from time to time.

    With the new structure, I would have the arrival and departure info in one record and all the services as a one to many. It would be easy to see all the services that have been ordered, I can apply a cost to each one and create an invoice, and basically seems like a better and more logical structure. My issue is still how to live summarize a status field for each type of service for a particular day.

    Leave a comment:


  • lgrupido
    replied
    Re: Database Design Thoughts, Help!

    In my initial response, I didn't realize it was a desktop app. From a purely database design perspective, I believe SQL outperforms a regular DB, especially when the db size gets large.

    But since I discovered it was a desktop app some panels back, I have taken myself out of the discussion because I don't know Alpha Desktop apps, so I'm not the best person to help here - and I agree that using passive or even active link tables is not the best way to go.

    Leave a comment:


  • Peter.Greulich
    replied
    Re: Database Design Thoughts, Help!

    John,

    Even though Larry wants you to use SQL, there's no great way to use it on the desktop. Active-link tables are kluge. That leaves you w. xdialog (fast!) or WCD. I just finished a small xdialog project using MySQL (fast!), but super-labor intensive. You have to code EVERYTHING! And WCD (w. sql) will be slower on the desktop than dbf.

    Leave a comment:


  • DaveM
    replied
    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.

    Leave a comment:


  • MoGrace
    replied
    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.

    Leave a comment:


  • DaveM
    replied
    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.

    Leave a comment:


  • DaveM
    replied
    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???

    Leave a comment:


  • johngtatp
    replied
    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?

    Leave a comment:


  • johngtatp
    replied
    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.

    Leave a comment:


  • MoGrace
    replied
    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.

    Leave a comment:


  • DaveM
    replied
    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.

    Leave a comment:

Working...
X