Results 1 to 8 of 8

Thread: theory

  1. #1
    Join Date
    Jan 2004
    Posts
    4

    theory

    just a theory question.

    Say I had a login screen which takes username and password. but i have two tables, customers and employees (both can log in)

    I know its easy to check both tables for the correct combo...but:

    How would I (using fk) make sure the same username was not in both tables? Is this the best design or is it better to have a table called "person" which stores everybody. It would have an extra column to store wheather the person was an employee or a customer?

    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "How would I (using fk) make sure the same username was not in both tables?"

    you can't

    "Is this the best design or is it better to have a table called "person" which stores everybody. It would have an extra column to store wheather the person was an employee or a customer?"

    that is better

    it is called supertype/subtype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    4
    Would the pk of the supertype table person bee the pk + fk of the subtype table?

    What would be the correct naming convention for this setup?

    person
    person_employee
    person_customer


    Which brings me to my other main "theory" question..In your expereince what is the best naming convention for table and column names?

    id or ID or PersonID or person_id

    I know these are trivial questions but I would rather get it right the first time...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "Would the pk of the supertype table person bee the pk + fk of the subtype table?"

    yes, that's right

    there is no "correct" naming convention

    the best naming convention, in my opinion, is

    - lean
    - short
    - evocative

    lean means don't throw crap into the name, like tblPeople for the people table

    short should be obvious, you will be typing the name, and the fewer keystrokes the better

    evocative to give an indication of what's in the table/column, so BackorderAmount instead of BOAmount (shorter but misleading) or -- $deity help us -- Amount (meaningless)

    it should also go without saying that you should not name a column with a reserved word like Date, but i see this all the time

    there is a bit of controversy surrounding the naming of primary keys

    if table1 has a pk called ID, and table2 is related to table1 with a foreign key, what do you call the foreign key?

    most people (myself included) would call it table1ID, but this prevents you from using the USING keyword in joins, and the NATURAL join altogether

    in order to use USING or a NATURAL join, you would have to go back to table1 and name the primary key table1ID, which i find a bit redundant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Posts
    4
    so a column can be a pk and a fk at the same time

    examle:

    person:
    personid | name
    5 | jon

    employee:
    personId | officeID
    5 | 10

    employee.personId is a pk and a fk, if not how do you stop a double entry:

    employee:
    personId | officeID
    5 | 10
    5 | 10

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, personID in the employee table is both pk in its own table and fk to the people table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Posts
    4

    Talking

    In your experiences, is it normal to have a supertype/subtype relationship where employees and customers can be stored in one supertype table. I am just trying to think of any problems that can arise in the future where it might need to be seperated.

    It seems that a database designer can "over-think" a database design....

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    normal?

    i dunno

    it depends on so many things
    rudy.ca | @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
  •