Results 1 to 7 of 7

Thread: Linking Records

  1. #1
    Join Date
    Sep 2002
    Posts
    79

    Unanswered: Linking Records

    I have a two forms and each form has its own table.

    One form is called “Customers” and the other is “Invoices”. Both forms are linked to each other.

    In the “Invoices” form, there is some information that is the same as “customers” (such as Company, name, address…ect.).

    What I want to do, is once I click a button in “customers” to open “invoices”, but, also at the same time to fill out some specific fields by itself. Now I know that would be easy if they both were going into the same table, but, I want all information to be in two different tables.

    How can this be done?

    I hope I didn’t make my question more complicated than it should be…

  2. #2
    Join Date
    Jan 2003
    Posts
    46
    Alex,

    Your question is fine and the answer is easy. You should not be duplicating information between tables.

    Having said that there may be some original and overwhelming reason why you are tyring to do so. Please let us know as there may be a better way to get to where you are trying to go.

    Cheers,
    zambezibill

  3. #3
    Join Date
    Sep 2002
    Posts
    79
    Why is duplicating records a bad thing - That is something i did not know.

    I just wanted things to be easy for future references. I mean, shouldnt content in Customers go into its own table and content from invoices go in its own?

    Two different forms, why not two different tables?

  4. #4
    Join Date
    Jan 2003
    Posts
    46
    I am not going to be able to do justice to relational database and normalization theories, but I will try to ramble through some brief explanations.

    Tables are for storing data, forms for entering and viewing data, while queries are used (among other things) for retrieving data from one or MORE tables.

    The proper setup of tables will allow maximum flecibility, maximize the speed of data retrieval, minimize errors, increase the ease of data entry and maintenance, and maximize your ability to retrieve data in its most meaningful form. Improper table structure will eventually hinder your application development (since ultimately everything else is built upon the tables) and once done will present a huge undertaking to correct. The extra couple of hours to initially get tables designed correctly may save weeks of future work. Always assume that an application will grow and become more demanding over time.

    Data should be non-repetitive. Data should be entered in its most atomic form.

    1234 East Main Street Chicago ILL 49186
    should ideally be split between 6 fields rather than one.

    Tables should be set up to hold related data. Whenever there is a one-to-many relationship between data, this is an indication that data should be split between 2 tables. A many-to-many relationship usually calls for three tables.

    it is the use of primary and foreign keys that maintains the relationship between tables and the data within.

    Duplicate data adds unnecessary bulk. It may restrict your ability to retrieve proper recordsets. And in the event a data entry error was made, you have a problem of locating all the instances of that data to make the corrections. You also end up with things like:

    1234 West Main St., Chicago, ILL
    1234 W. Main, Chicago, ILL
    11234 West Main St., Chicago, ILL

    Are these errors? or are they 3 correct and different addresses? Once this sort of data entry corruption occurs, it is almost impossible to clean up.

    So back to your situation. One customer - many invoices (at least potentially). So two tables (at least). And it then follows that you will need two forms to present the data. Actually yours is a standard situation that every programmer runs into at some point. And you are probably going to need many tables (get used to the idea).

    Table 1) Customer names (believe it or not, customers come with multiple names)
    2) Customer addresses (mailing addresses, physical addresses, PO Boxes)
    3) Phone numbers
    4) Customer contacts (over time you will develop numerous contacts)
    5) Invoices (Dates, shipping info, payment terms, etc.
    6) Invoice line items (One invoice may have numerous parts, items, etc.)
    7) Customer contact notes

    This just touches on the basics and I will leave you to scratch your head.

    Cheers,
    zambezibill

  5. #5
    Join Date
    Sep 2002
    Posts
    79
    Why do I need 7 different tables? Wont that just cause more headache?

  6. #6
    Join Date
    Nov 2002
    Location
    Tidewater, VA
    Posts
    8
    The number of tables really does not matter, just that the right data is in them. I have a table that contains fields that lookup to another table with data that doesn't change. For example, a table with state abbreviations. The state table would contain the constants and then my address table would have a lookup field called 'state' that got the value from the state table. This actually makes things easier for the user.

    Hope this helps.

  7. #7
    Join Date
    Sep 2002
    Posts
    79
    How about my first question? Can what i am looking for doing be done and, if so, how?

Posting Permissions

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