SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table setup advice needed

    I'm trying to setup a billing table that will track entities that are paid, total paid, and date paid. My database contains about 10 different tables that contain different entity types. For example employers, attorneys, doctors, farmers, etc. In order to program my billing form in the html page I was going to have the entity's record id number(autonumber) saved in an integer field of the billing table. But because I have different entity types all saved in different tables, how would I distinguish the different entity types when inputting the information and when creating reports. For example, there maybe a employer, a doctor and a attorney all with a record id of 2. When I create a report how would I set this up so the appropriate one would be pulled?

  2. #2
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I create a report how would I set this up so the appropriate one would be pulled?
    hi
    Use Primary and foreign keys, then Use JOINS to join the tables together.

    finally, use the select statement with a where id = Primary key to retrieve the required records.
    rgds
    Afrika

  3. #3
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure you understand the problem. I'll try and explain it again a little better.

    I have a table calls tblbills, here are the fields
    col1=autonumber field
    col2=payto (integer field)
    col3=$ amount (currency field)

    Currently my database has about 10 other tables all with the exact same structure:
    tbldoctor
    col1=autonumber field
    col2=docotr name(text field)

    tblattorneys
    col1=autonumber field
    col2=attorney name(text field)
    etc, etc

    So when I enter a new bill in tblbills, I'd like to put the col1 value of the entity I'm paying the bill to(such as tbldoctor.col1 or tblattorney.col1). But, how can I keep track of who's getting paid. The integer value won't be enough since it could be from tbldoctors, or tblattorney. How do other systems normally handle this?


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
  •