Alpha Video Training
Results 1 to 22 of 22

Thread: Database Design Thoughts, Help!

  1. #1
    Member
    Real Name
    John Tary
    Join Date
    Jan 2007
    Posts
    418

    Default 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. #2
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,994

    Default 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
    dave@aldaweb.com

    Skype is dave.mason46

  3. #3
    "Certified" Alphaholic
    Real Name
    Larry Grupido
    Join Date
    May 2010
    Posts
    1,718

    Default 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.

  4. #4
    Member
    Real Name
    John Tary
    Join Date
    Jan 2007
    Posts
    418

    Default 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 Images Attached Images

  5. #5
    "Certified" Alphaholic
    Real Name
    Larry Grupido
    Join Date
    May 2010
    Posts
    1,718

    Default 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.

  6. #6
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,994

    Default 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
    dave@aldaweb.com

    Skype is dave.mason46

  7. #7
    "Certified" Alphaholic
    Real Name
    Larry Grupido
    Join Date
    May 2010
    Posts
    1,718

    Default 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.)

  8. #8
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,994

    Default 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
    dave@aldaweb.com

    Skype is dave.mason46

  9. #9
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,595

    Default 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

  10. #10
    Member
    Real Name
    John Tary
    Join Date
    Jan 2007
    Posts
    418

    Default 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.

  11. #11
    Member
    Real Name
    John Tary
    Join Date
    Jan 2007
    Posts
    418

    Default 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?

  12. #12
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,994

    Default 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
    dave@aldaweb.com

    Skype is dave.mason46

  13. #13
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,994

    Default 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
    dave@aldaweb.com

    Skype is dave.mason46

  14. #14
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,595

    Default 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 at 02:55 PM.
    Robin

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

  15. #15
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,994

    Default 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
    dave@aldaweb.com

    Skype is dave.mason46

  16. #16
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,629

    Default 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.

  17. #17
    "Certified" Alphaholic
    Real Name
    Larry Grupido
    Join Date
    May 2010
    Posts
    1,718

    Default 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.

  18. #18
    Member
    Real Name
    John Tary
    Join Date
    Jan 2007
    Posts
    418

    Default 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.

  19. #19
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,595

    Default 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?
    Robin

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

  20. #20
    Member
    Real Name
    John Tary
    Join Date
    Jan 2007
    Posts
    418

    Default 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.

  21. #21
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,595

    Default 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?
    Robin

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

  22. #22
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,994

    Default 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.
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

Similar Threads

  1. Database Design Question
    By bluize in forum Database Design
    Replies: 7
    Last Post: 12-24-2009, 11:38 AM
  2. Database Design Help
    By johngtatp in forum Alpha Five Version 9 - Desktop Applications
    Replies: 20
    Last Post: 05-14-2009, 03:25 AM
  3. A Little Database Design Help
    By Rudy16 in forum Database Design
    Replies: 2
    Last Post: 10-25-2008, 11:26 PM
  4. New database design
    By Marcel Kollenaar in forum Database Design
    Replies: 1
    Last Post: 04-19-2008, 12:36 AM
  5. Database design - Form Design
    By Larpup in forum Alpha Five Version 8
    Replies: 2
    Last Post: 06-11-2007, 11:45 AM

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
  •