Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004

    Unanswered: Design -- Should this be split up into a few tables?

    I'm grappling with this design problem right now:

    I have a table of users. Every user has an e-mail address and (hashed) password. Some of those users work for a company, and some of them do not. Of those who do not work for a company, some are salespeople who sell to one or more companies. Some users are simply administrators who don't work for a specific company. So here's what my users table looks like right now: "UserID, Email, Password, CompanyID (Nullable), IsAdmin"
    And here's my companies table: "CompanyID, CompanyName, SalespersonID"

    Of course, I could separate it out and make a Users table, an Employees table, and a Salespeople table. The way the relationship works out, though, I could use the same ID number for all three tables, and that indicates to me that perhaps they all belong in the same table. It seems silly, after all to have a Salespeople table whose only field is "UserID."

    Two factors of the first design concern me: First is the fact that a salesperson could also have a company. I guess I could write a check constraint to prevent this, but doesn't having the companyID in the Users table violate a normalization rule? Maybe? The second is the fact that the Companies table relies upon Users, which in turn relies upon Companies. In OOP, this usually isn't a good thing, but I'm not sure whether it's cause for concern in a relational database.

    Anyway, I really don't know what I should be doing with this design. Any suggestions?

    Thanks in advance,

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by starwiz
    And here's my companies table: "CompanyID, CompanyName, SalespersonID"
    this suggests that each company can have one and only one salesperson at any given time

    sounds to me like you really need a usercompany table

    primary key would be composite: userid plus companyid

    that way, a user can be a salesperson for more than one company, and, more importantly, a company can have more than one salesperson

    the company table would just have companyid and companyname | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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