Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2009
    Posts
    33

    Unanswered: How do I use Relational Tables properly

    Ok, I have a database issue that I cannot seem to grasp, maybe I just don't understand it, but regardless, I think the issue should be simpler than it actually appears to be.

    In a nutshell, I have a master table with a dozen fields (customer) then I have 5 additional tables. Some have information, some don't, but all will eventually.
    I programmatically create a relationship between customer.id and orders.id, payments.id and the remaining tables. Access shows the table relationships correctly.
    "Customer" will have only 1 entry per customer, while the remaining tables will have multiple entries per customer, thus defining a one-to-many relationship between the master table and child tables.
    When I query the database i.e. (SELECT * FROM Customers), I don't get the related fields. I understand that I must query all the tables ... but when I do, I either get no records (because the child table is empty) or I get the number of records in the customer table multiplied by the number of records in each of the subsequent tables.

    So, to start with I have 5 customers in the master table, with 5 items in each of the 5 child tables. I end up with 15625 records, clearly I don't want every possible permutation of records. I merely want to retrieve the records from each table as they relate to the record in the master table.

    For example what might be returned when issuing the following query
    (SELECT * FROM Orders Where ID = CustomerID)
    but I need this for at least 5 tables, and then I need to display this data in a DataGrid.

    It certainly seems complicated to me. I am hoping someone will show me the errors of my ways and explain this in a way I can understand.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Interesting question. I think you could probably do with reading a book or tutorial. The reason is you appear to know some really pretty advanced stuff, but not know some elementary stuff too. Sounds like you probably, therefore, have a few gaps in your knowledge.
    Quote Originally Posted by keb1965
    So, to start with I have 5 customers in the master table, with 5 items in each of the 5 child tables. I end up with 15625 records, clearly I don't want every possible permutation of records. I merely want to retrieve the records from each table as they relate to the record in the master table.
    This is called a Cartesian Product and is because every row in every table is joined to every row in every other row in every table in the query.

    What you need are JOINS, specifically LEFT OUTER JOINS. A left outer join selects all records from one table, and only those that match from another. Easiest way to do this in Access is using the Query Builder graphical view, although you can write the SQL. You would join the primary key of your master table to the foreign keys of the child tables.

    See how you get on and report back.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2009
    Posts
    33
    First, let me thank you for taking the time to help me understand this better.

    I understand what you are saying, and I am familiar with a Cartesian Product .. which is what I want to avoid. I can join the tables but I can't imagine not having duplicate customers.

    While I can see how a join would reduce the records from 15k, wouldn't there be duplicates where a child table had multiple matching records?

    I suppose what I am looking for is an easy method for data mangement, but unfortunately it looks like I will have to roll my own handlers.

    Maybe if I provide a little more information on what I am doing, you can point me in the right direction on how I SHOULD be handling this.

    I am building a dynamic front end, where all queries are handled programmatically. The front end will have 3 user customizable toolboxes. Each of these toolboxes contain the following other controls:
    Toolbox 1 has a combo box, text box, and listview (although I can make it a DataGridView) with 2 columns
    Toolbox 2 has a multi-line textbox (for notes) and a datagridview with 2 columns
    Toolbox 3 has two multi-line textboxes

    Each toolbox, after having been defined by the user is stored in a toolbox database that is loaded dynamically when the front end loads.

    Each toolbox has its own table in the "projects" database

    Toolbox 1 will only store the listview data. Since it is multi-columned and multi-rowed, and since the only static thing about this data is the number of columns, I need to have a "one-to-many" relationship with any Toolbox1 tables, as the table by neccessity will have only 2 columns, with multiple matching records per "project" .. plus of course the autonumber field and FK

    Toolbox 2 will store the data from the multi-line textbox (one field) and only one column in the DataGridView. The number of rows in any particular instance of Toolbox2 will be static, so I can easily create a one-to-one relationship. For example, there may be 5 rows in the datagridview, so I will have 6 user fields, one autonumber and one FK. This would work fine except, I need the record data to be bound to the DataGridView column. This is where I have to roll my own handler, I suspect.

    Toolbox 3 will store the data from the 2 multi-line textboxes in a one-to-one relationship. i.e. autonumber, Text1, Text2, FK

    If all I had were straight one-to-one relationships, all would be perfectly fine, but I don't, and I have to be able to not only display the data, but edit it and save it back to the "projects" database.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well I think then you want a header forms and sub forms. I'm not 100% sure, but that's how it sounds.

    So if you have:
    Client table
    Code:
    ClientName----------ClientAddress
    pootle flump---------flumpington manor
    orders table
    Code:
    ClientName----------Product----------Quantity
    pootle flump---------caviar-----------50
    pootle flump---------fois gras---------100
    pootle flump---------peacock---------27
    then what would want to actually display?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2009
    Posts
    33
    For this scenario with only one "one-to-many" relationship:

    Customer Table
    HTML Code:
    ┌────────────────┬────────────────┬─────────────────┐
    │ClientID        │ClientName      │ClientAddress    │
    ├────────────────┼────────────────┼─────────────────┤
    │1               │pootle flump    │flumpington manor│
    │2               │keb1965         │kebville         │
    │3               │the donald      │trump towers     │
    │4               │ellie mae       │hollywood        │
    └────────────────┴────────────────┴─────────────────┘
    Orders Table
    HTML Code:
    ┌──────┬──────────────┬─────────┬──────────┐
    │ID    │Description   │Quanity  │ClientID  │
    ├──────┼──────────────┼─────────┼──────────┤
    │1     │caviar        │50       │1         │
    │2     │fois gras     │100      │1         │
    │3     │peacock       │27       │1         │
    │4     │caviar        │15       │2         │
    │5     │fois gras     │2        │2         │
    │6     │peacock       │1        │2         │
    │7     │caviar        │3        │3         │
    │8     │fois gras     │50       │3         │
    │9     │peacock       │7        │4         │
    └──────┴──────────────┴─────────┴──────────┘
    I need to display in 3 different controls:

    HTML Code:
    Name:
    ┌─────────────────────────────────┐
    │pootle flump                     │
    └─────────────────────────────────┘
    Address:
    ┌─────────────────────────────────┐
    │flumpington manor                │
    └─────────────────────────────────┘
    Order:
    ┌────────────────┬────────────────┐
    │Product         │Quanity         │
    ├────────────────┼────────────────┤
    │caviar          │50              │
    │fois gras       │100             │
    │peacock         │27              │
    └────────────────┴────────────────┘
    I suppose alternatively, I could invert the rows and columns in the orders table then have a true one-to-one relationship, except using real data, the number of "products" will change with each subsequent "customer" and we have no way of knowing all of the products that will be added, dropped, substituted, etc. plus the maximum number of columns in a table is limited to 255. I can easily see this getting much larger than that.

    So, I can see the need to query the customer table like so:

    SELECT * FROM Customer WHERE ClientID = 1

    This will return a single record:
    HTML Code:
    ┌────────────────┬────────────────┬─────────────────┐
    │ClientID        │ClientName      │ClientAddress    │
    ├────────────────┼────────────────┼─────────────────┤
    │1               │pootle flump    │flumpington manor│
    └────────────────┴────────────────┴─────────────────┘
    Then I can query the order table
    SELECT * FROM Order WHERE ClientID = 1

    This will return multiple records:
    HTML Code:
    ┌──────┬──────────────┬─────────┬──────────┐
    │ID    │Description   │Quanity  │ClientID  │
    ├──────┼──────────────┼─────────┼──────────┤
    │1     │caviar        │50       │1         │
    │2     │fois gras     │100      │1         │
    │3     │peacock       │27       │1         │
    └──────┴──────────────┴─────────┴──────────┘
    Ok, great ... so what does a relationship do for me, other than identify the Client in the Order table and how can I leverage queries for efficiency?

    I'd appreciate any insight you might have to offer.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    thre realtionship forces data integrity
    it makes sure your data is cohesive, properly formed
    it makes it easier when usignthe query designer to join together tables.

    as Pootle has already suggested I suspect you form design is going to be a ,aster & sub form
    the masterform will contain all details of the customer (eg customerID, date, delivery date address and so on
    the sub form will contain details of products ordered for that customer, where you can have many otems for any one customer in any one order

    realtionships are about enforcing identifiable relationships

    for a customer order you need the following data from other tables
    the customer name, their invoice address, their delivery address.. from the customer table
    details of the product(s) ordered (fromt he products table, but identified int he order details table, plus the reast of the order details (such as quantity, price etc.

    relationships are not some magical tool, however they do amke system designing easier, and they also help developers and DBA's sleep at night (or in a DBA's case the daytime too). properly designed they reduce the risk of users entering duff data, screwing up the the data.,

    you can design a system without the realtionship but it means you have to do more work. more testing, and in a system like JET (the default data store for Access) you cna never be sure that the business rules are accurately enforced unless you use relationships.

    you can write code that replaicate the function of relationships, but as I read it there seems no point in doing so especially as RI constraints are bulit into relatiional dbms's
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2009
    Posts
    33
    Are you suggesting that I don't try and query the relational database items until I actually require them?

    For example, merely query the Customer table and find the customer I need,

    "SELECT * FROM Customer Where CustomerID = 1"

    then using the returned row (the customers will all be unique)
    CustID = .Rows(0).Columns("CustomerID")

    "SELECT * FROM Orders Where CustomerID = " & CustID

    Finally, I bind the Customer info to the customer controls, such as name, address etc. then bind seperately the Order info to my DataGridView?

    Given that scenario, if I have a dozen different tables to query, I have to make a dozen different queries, unless of course I do a strict join on the one-to-one tables and query seperately the one-to-many tables.

    So, how might I properly join one-to-one tables so I have a single query with a single record, and still return a record even if the relational table has null values for a particular field. So far, if the related tables have NULL in any particular field, no record is returned, even if there is a customer, and how does that work into creating new records with joined queries. For example:

    SELECT * FROM Customer WHERE CustomerID = -1

    I am beginning to understand this so much more. I appreciate your patience.

  8. #8
    Join Date
    Feb 2009
    Posts
    33
    Ok, I have made some progress. After doing some more reading, I am beginning to understand so much more.

    The query works exactly as expected for the one-to-one tables. I only have (1)- one-to-many table and I can manage that independently from the one-to-one tables. However, there is an issue with saving the records back to the database. The error is that the command builder cannot work across multiple tables. I presume that it cannot know which table the field came from. Would using a specific table-field name resolve that? For example, right now I use this query:

    TableList = "table1, table2, table3, table5"
    RecordIDFilter = "table1.ID = table2.ID AND table1.ID = table3.ID AND table1.ID = table5.ID"

    "SELECT * FROM " & TableList & " WHERE " & RecordIDFilter

    For that query all of the proper records are returned. But the field names returned are not qualified by table name. I can still bind and manipulate the data within code easily, but the command builder won't save across multiple tables.

    I cannot hard code the table and field names into the query as they will be changing regularly, and although I currently know all of the names of my tables and fields, they will grow and I will no longer know.

    I guess I need to roll my own command builder, but I can see that getting pretty unwieldy as the number of tables and fields grow.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    I cannot hard code the table and field names into the query as they will be changing regularly
    best of luck!
    there have been many attempts at "the universal form" (query, table, whatever) - a total waste of time.

    if you are unfortunate enough to be offered responsibility for a system, my recommendation is that you demand total control of the system as a precondition of your acceptance. your will grow and I will no longer know is a recipe for an early grave.

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    "table and field names into the query as they will be changing regularly,"

    Ugh!

    Agrees with izy.

    By enforcing relational integrity within the database, you can eliminate orphaned records, and simplify the clients. And, you could go on vacation, and not have to worry that the temp who was hired to work on a new and improved client widget doesn't KNOW that you can't add a record to the details record without having a master record first...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  11. #11
    Join Date
    Feb 2009
    Posts
    33
    Well, I suppose therin lies the problem.

    First, I will not be administering the database ... in fact, I don't know that anyone will ... outside of the front end that I am writing to manage all data transactions. Secondly, due to the environment where the application will be deployed, it must be as idiot proof as possible.

    I know those are high orders ... but it will be done, I just have to figure out the best and most efficient method.

    I'll try and explain the concept a little more clearly, and perhaps you will understand my dilemna.

    The application will have a few prebuilt toolboxes, each with the various fields already defined in the back end. These may be deleted at will by the administrator. Additional toolboxes may be defined as well, however, the template must be followed and there are a limited number of fields for each toolbox. When a new toolbox is added, if the matching table and/or fields don't exist, they are created. When a toolbox is removed, all tables and fields are subsequently removed as well.

    The database will not be able to be edited directly and as such there is little worry of a rogue know-it-all mucking things up.

    The toolboxes are built as ActiveX components and as such will manage their own data, such as binding and updating to the master record. The application merely has to update the master record to the database.

    Since I can't hard code the table names or fields, I'll have to obtain the toolbox ID from my toolbox database, then find the matching table in the database and write my update code dynamically using that information. I can do that, I just hoped I wouldn't have to.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •