Alpha DevCon 2018
Results 1 to 6 of 6

Thread: SQL Table Relationships

  1. #1
    Member
    Real Name
    C. Michael Roberson
    Join Date
    Aug 2009
    Posts
    56

    Default SQL Table Relationships

    I am attempting to create some table relationships for a SQL backend database (using Alpha Anywhere for the frontend) that handles service activity on a work order ticket. I have one-to-many relationships for these tables and have a question.
    I have WOT (Work Order Ticket) table for the creation of a work order ticket.
    The WOT can have multiple service activities on different dates associated with it.
    Each service activity can have multiple parts used (for a repair).
    This is just the basic idea, but I have created the following tables and relationships:

    WOT Table:
    wot>wotnum -PK

    Service Activity Table:
    service_activity>serviceid – PK
    service_activity>wotnum – FK (link to PK in WOT table)

    Part Used Table:
    partused>partusedid – PK
    partused>serviceid – FK (link to PK in Service Activity table)

    Each of the tables above has other columns as well (not shown), but they are unique to the table, such as date fields, part numbers, etc.

    The service_activity>serviceid (PK) field is an autoincrement field and so is the partused>partusedid (PK) field as well.

    My question is, during data entry, how do I insure the partused>serviceid field (FK) s synced with the service_activity>serviceid field (PK) without actually having to manually enter the partused>serviceid field (FK)?

    Although I have a decent understanding of tables and relationships (critical to get this correct now), I am a bit of a neophyte as to the process of thinking through how the tables will interact during actual data input. I think the answer to this may be simple, but I am just not grasping it yet. If my current solution does not seem adequate, I would welcome a suggestion. I need some help to get going in the right direction.
    Last edited by gitpicker; 09-22-2017 at 01:14 PM. Reason: All original info did not get saved(posted)

  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: SQL Table Relationships

    You mention using Alpha Anywhere for the frontend. Do you intend to access the sql tables via Alpha active linking? If so you can create sets linking the tables. When working with layouts based on these sets and entering data into browses of child tables on those layouts Alpha will automatically fill the linking field in the child table.
    Last edited by Stan Mathews; 09-22-2017 at 01:56 PM.
    There can be only one.

  3. #3
    Member
    Real Name
    C. Michael Roberson
    Join Date
    Aug 2009
    Posts
    56

    Default Re: SQL Table Relationships

    Awesome! Thank you Stan. That is what I needed to know.

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

    Default Re: SQL Table Relationships

    Michael, think carefully about using SQL as a back end for Alpha Desktop.
    There are good reasons why Alpha has a DBF database structure. It is easy to manipulate and runs quickly.
    Using SQL requires a different Db management tool and the performance may degrade as the record count increases.
    Recent tests I have run suggests a 10 second delay loading a large tabbed form using Active Link, and a 1 second load using native DBF.
    Ted Giles
    Example Consulting - UK
    .

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

  5. #5
    Member
    Real Name
    C. Michael Roberson
    Join Date
    Aug 2009
    Posts
    56

    Default Re: SQL Table Relationships

    Ted, thank you for your input. It is duly noted. Technically, you are correct. However, if I create SQL tables, I can use some other front end for development as I might choose. If I create a DB using DBF in Alpha Five, I am tied to A5 forever for that DB. Alpha Software has changed quite a bit over the years concerning their support for their customer base. Alpha Software may not be my DB of choice in the future. I am leaving my options open. Having said that, I still like Alpha Five and have some familiarity with it. I am not a "real" DB programmer or developer. I develop a DB as I have a need for one in my business. I enjoy the challenge and, so far , have created two databases that are working pretty well, one is a desktop using DBF, and one is web based using SQL. As well, I am teaching myself SQL, and this particular DB will never get that large.
    This is what I really love about the Alpha software forum. Alpha Five is great, but the people are awesome! For you to reply to my request in the way that you did shows me how much you care about people you don't even know! I have been blessed over an above by the people on this forum. I have people going out of their way to help me, and that feels pretty good. Thanks Ted!

  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,066

    Default Re: SQL Table Relationships

    I really do understand. The problem I have with Alpha DT and SQL is performance. The concept is outstanding, but the delivery over DT is not always good.
    SQL opens a whole new vista of options.
    DBF doesn't seem to have morphed into the quick performance Db that it possibly could be.
    Ted Giles
    Example Consulting - UK
    .

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

Similar Threads

  1. Handling Table Relationships
    By RhinoSmith in forum Application Server Version 11 - Web/Browser Applications
    Replies: 4
    Last Post: 11-29-2012, 07:55 PM
  2. Help with M-M relationships
    By crichards in forum Alpha Five Version 10 - Desktop Applications
    Replies: 1
    Last Post: 10-12-2010, 06:21 PM
  3. Many to Many Relationships
    By MikeBalemi in forum Alpha Five Version 8
    Replies: 2
    Last Post: 12-05-2008, 05:17 PM
  4. Many to Many relationships
    By Ted Giles in forum Alpha Five Version 7
    Replies: 16
    Last Post: 10-18-2007, 11:40 AM
  5. 1 to many relationships
    By gagsoft in forum Alpha Five Version 6
    Replies: 3
    Last Post: 03-01-2005, 08:58 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
  •