Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187

    Unanswered: Help with design of tables

    Hi all,
    I need to design tables and the relationships between them and I'm having a brain cramp. PLease help.
    I have shippers, report types, and users.
    A shipper is assigned to a report type>>>>1 to 1
    A User can be assigned to multiple report types, once user is assigned he can pick which shippers he can see for a report type.
    Does that make sense?
    I'm having a brain cramp and can't figure out how to build the tables with those relationships. Do I need some sort of LookUp table???
    Please help with a design.

    Thanks.

  2. #2
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    To tie the Users to the Report Types you would need another table, say UserReportType, which would have a UserID field + a ReportTypeID field.
    The primary index would be those 2 fields.
    The UserID field would be the PI on the User table + the ReportTypeID the PI on the ReportType table.

    As the Shipper can only have 1 Report Type, then the ReportTypeID could be contained on the Shipper table.

    So 4 tables are required.

    User
    ReportType
    UserReportType
    Shipper

    So given a UserID to get a list of all Shippers, you would just query the UserReportType, User + Shipper tables using an INNER JOIN. Take a look at SQL Books online for the SELECT/JOIN syntax

    Mark

    Mark

  3. #3
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Help with design of tables

    Originally posted by bpolunin
    Hi all,
    I need to design tables and the relationships between them and I'm having a brain cramp. PLease help.
    I have shippers, report types, and users.
    A shipper is assigned to a report type>>>>1 to 1
    A User can be assigned to multiple report types, once user is assigned he can pick which shippers he can see for a report type.
    Does that make sense?
    I'm having a brain cramp and can't figure out how to build the tables with those relationships. Do I need some sort of LookUp table???
    Please help with a design.

    Thanks.
    Can a report type have more then one user? If yes then you will need a many-many join using an intermediate table to create the relationship. Otherwise a one-many relationship will do between the two.

    Can a shipper be assigned to more then one report type? If yes then a one-many relationship exists. If a report type can also have many shippers then a many-many relationship exists. If no to both of these then you could just use the shippers table as a look-up table.

  4. #4
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Thanks for the reply guys.

    mtracey: That much I have, but I can't get how to assign certain shippers in a report type to a User. I don't want all shippers in a report type to a user. Lets say I have 5 shippers assigned to Test Report. Joe is assigned to run Test report, but Joe is allowed to see only 2 out of 5 shippers for that report.
    That's what the trouble is.

    justin_tighe: Yes, multiple users can access multiple reports, and also this part I already have, but the trouble is the users to shippers. Read above.

    Thanks guys.

  5. #5
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    Would you need to have another table to restict which Shippers, Users can see ?

    More understanding of the business requirements would help I guess.

  6. #6
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    OK, I'll try again.
    I have users, reports, and shippers.
    I assigned 5 shippers to Report Test
    I assigned Joe to access Report Test
    I also assigned Sam to access Report Test
    When Joe runs the report he need to get records from 3 shippers.
    When Sam runs the report he need to get records from 2 shippers.

    Then I assigned Pam to run Report Test.
    When Pam runs the report she need to get records from all shippers.

    I hope this explains a bit better. So I'm thinking that I will need another table.

  7. #7
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    If I'm reading it correct, then you need a table to link Users to Shipper, thus restricting the Shipper information which the Users see.

    UserShipper, with UserID + ShipperID as PI.

    If you wish I can work out the joins for this, but it'll be tomorrow before they'll be ready.

  8. #8
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    I appritiate your help guys, I got it now, my brain uncramped
    I created a table with UserID, ShipperID, and ReportID.
    And in there I have

    UserID ShipperID ReportID
    1 1 1
    1 2 1
    1 3 1
    2 4 1
    2 5 1
    2 2 1
    3 6 2

    This sort of thing but much bigger. I ended up with 68887 records.

    Thanks again.

Posting Permissions

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