Alpha Video Training
Results 1 to 7 of 7

Thread: Examples of ways to establish relationships with Data in two different tables?

  1. #1
    Member
    Real Name
    Adam
    Join Date
    Sep 2010
    Posts
    6

    Default Examples of ways to establish relationships with Data in two different tables?

    Looking for some refresher info and quite possibly a video of two...

    Trying to figure out the best way to related data between a table called Drivers and a table called vehicles. There could be many drivers for a vehicle or many vehicles for a driver.

    Also,

    How can I carry over information from one form to a next....

    For instance I have a table called incidents which has date, time, location of events, and Event ID(numeric Generated in another system hand keyed in) that occurred. I want to attach multiple drivers and vehicles to this incident. I envision having a button to "Add Driver" and "Add Vehicle" I would want to have the case number from my parent table(incidents) attached to each driver and vehicle.

    This is DB 101 for me, but I sometime have a hard time establishing the best way accomplish this.

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Examples of ways to establish relationships with Data in two different tables?

    Trying to figure out the best way to related data between a table called Drivers and a table called vehicles. There could be many drivers for a vehicle or many vehicles for a driver.
    There are three "normal" possibilities.
    Driver as parent, vehicles as child, linked one to many on vehicle_id. Vehicle as parent, driver as child, linked one to many on driver_id.

    An intermediate table can be created with all the possible combinations of driver_id and vehicle_id and both tables linked to this new table on the appropriate id. Which you choose for what purpose is at your discretion.

    Your description leads me to believe that those normal sets might not serve you best. Others with more experience in this area will undoubtedly chime in.
    Last edited by Stan Mathews; 07-28-2011 at 11:33 PM.

  3. #3
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Examples of ways to establish relationships with Data in two different tables?

    How can I carry over information from one form to a next....
    No clue what that means. Forms are based on tables or sets. Information resides in tables. A form is a means of displaying one record from a table or one parent record and the related child records in a browse of the child table on the form.

    What would you want carried over from where?

  4. #4
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: Examples of ways to establish relationships with Data in two different tables?

    Here's a database schema that may be of assistance:

    http://www.databaseanswers.org/data_...kets/index.htm

    Seems to me that in addition to the many-to-many relationship between drivers and cars you have:

    a) many-to-many relationship between drivers and cases; and

    b) many-to-many relationship between cars and cases.

  5. #5
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: Examples of ways to establish relationships with Data in two different tables?

    I would do it this way;-
    At any given event, there may well be more than one vehicle, and therefore more than one driver, but there can only be one driver per vehicle at the time of incident. Your incident table will have date, time, location of events, and Event ID as well as a field for driver and one for vehicle.
    1. The set to log a report uses this table is the main one - 1-1 links to driver table and vehicle table if you want to reference those for lookup. Additional vehicle driver combo gets an additional entry on the same incident number.
    2. The set can also be used for incident queries .
    3. To query driver/s use a 1-m driver-incident set,
    4. or vehicle/s use a 1-m vehicle-incident set.
    Last edited by Ray in Capetown; 07-29-2011 at 09:45 AM. Reason: edit

  6. #6
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,367

    Default Re: Examples of ways to establish relationships with Data in two different tables?

    One of the interesting things about Alpha, is the ability to create lots of Sets to display data in almost any way you wish without incurring any serious processing overheads.
    Unlike RPGII which got slowed down the more Logical Views (read Sets) were created, Alpha doesn't suffer from this sort of slow down.
    It takes a leaf of faith, and a few purists might balk at turning a Set on it's head, but for me, if that is the easist way to get a system in front of a Client, I'll go with it.
    If your background is in Access then you will need to "unlearn" the Access approach.

    Have a "butchers hook" at the attached Incidents DBF. Start with the Vehicles Set, add a new Driver, then see what happens to the Driver Table. Should get you started.
    Attached Files Attached Files
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  7. #7
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,201

    Default Re: Examples of ways to establish relationships with Data in two different tables?

    And even more interesting is the capacity to produce sets with recursive links. I do this frequently to fulfill the Many:to:Many needs as is desired by Adam, I believe.
    Attached Files Attached Files
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

Similar Threads

  1. Exporting to SQL-Database: Missing relationships between tables
    By flippah358 in forum Application Server Version 9 - Web/Browser Applications
    Replies: 4
    Last Post: 03-04-2009, 02:04 PM
  2. Any good examples of querystring parsing and data input?
    By Fulltimer in forum Application Server Version 8
    Replies: 5
    Last Post: 11-26-2007, 11:05 PM
  3. Alpha Five lets you work with your data in ways that are familiar to you.
    By Dick Anderson in forum Alpha Five Version 7
    Replies: 13
    Last Post: 06-23-2006, 07:10 PM
  4. How to establish Time Fields
    By leanne in forum Alpha Five Version 6
    Replies: 3
    Last Post: 11-01-2005, 09:31 AM
  5. Exporting - Possible Different Ways
    By jpayne in forum Alpha Five Version 4
    Replies: 2
    Last Post: 03-28-2002, 09:03 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
  •