Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002
    Posts
    3

    Question Is this a clever way of DB design or it can cause problem?

    I have two tables:

    Table 1: Employees
    -EmployeeID (AutoNumber)
    -LastName
    -FirstName
    -BirthDate

    Table 2: ProjectAssign
    -ProjectID (Integer)
    -EmployeeID (Integer)

    Usually the database designer will set EmployeeID as primary key for [Employee] and set the relation as One-To-Many on [Employee]![EmployeeID] and [ProjectAssign]![EmployeeID]. In this setup, there is no entity integrity checking for [LastName],[FirstName] and [BirthDate] on Table [Employee].

    But I set [LastName],[FirstName] and [BirthDate] as a composite primary key for [Employee] and set [Employee]![EmployeeID] as unique-indexed, and set the relation as One-To-Many on [Employee]![EmployeeID] and [ProjectAssign]![EmployeeID]. Now I will have not only referential integrity for [ProjectAssign]![EmployeeID] but entity integrity for [LastName],[FirstName] and [BirthDate] as well.

    Is this an extra benefit or it can cause potential problem using this kind of unconventional design?

    Thank you.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Is this a clever way of DB design or it can cause problem?

    You have it the wrong way round. EmployeeID should still be the primary key, as it is used as the foreign key in ProjectAssign and other tables. Then you can create a Unique constraint on LastName, FirstName, BirthDate to enforce that rule.

  3. #3
    Join Date
    Nov 2002
    Posts
    2

    Re: Is this a clever way of DB design or it can cause problem?

    Can you explain what you mean by "Unique constraint"? Is this that would happen outside of the database (e.g logic in a programming language)?

    Thanks,

    -Robin

    Originally posted by andrewst
    You have it the wrong way round. EmployeeID should still be the primary key, as it is used as the foreign key in ProjectAssign and other tables. Then you can create a Unique constraint on LastName, FirstName, BirthDate to enforce that rule.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Is this a clever way of DB design or it can cause problem?

    Originally posted by robin
    Can you explain what you mean by "Unique constraint"? Is this that would happen outside of the database (e.g logic in a programming language)?

    Thanks,

    -Robin
    A Unique constraint is declared in the database:

    ALTER TABLE person ADD CONSTRAINT emp_uk UNIQUE (surname, forename, birth_date );

    This will prevent any attempt to insert or update the table so that there are two people with the same name and date of birth.

  5. #5
    Join Date
    Nov 2002
    Posts
    3

    Re: Is this a clever way of DB design or it can cause problem?

    [QUOTE][SIZE=1]Originally posted by andrewst
    Thank you, Tony.

    Are there any shortcomings setting up that way? ([LastName],[FirstName] and [BirthDate] as a composite primary key)

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Is this a clever way of DB design or it can cause problem?

    [QUOTE][SIZE=1]Originally posted by Jessie
    Originally posted by andrewst
    Thank you, Tony.

    Are there any shortcomings setting up that way? ([LastName],[FirstName] and [BirthDate] as a composite primary key)
    I would NOT advocate using (LastName, FirstName, BirthDate) is a composite PRIMARY key, because:
    a) it is too cumbersome
    b) it is likely to get updated - e.g. due to marriage, or even just because someone says "you spelt my name wrong". Primary keys should not be updatable as a rule.

    I would have a surrogate key like PersonID as the primary key, and (LastName, FirstName, BirthDate) as a non-primary, UNIQUE key. The unique key is there just to ensure same person is not set up twice with different PersonIDs. Any tables that require a foreign key to person will have a PersonID column, they will not have (LastName, FirstName, BirthDate).

  7. #7
    Join Date
    Nov 2002
    Posts
    3

    Re: Is this a clever way of DB design or it can cause problem?

    Originally posted by andrewst
    A Unique constraint is declared in the database:

    ALTER TABLE person ADD CONSTRAINT emp_uk UNIQUE (surname, forename, birth_date );

    This will prevent any attempt to insert or update the table so that there are two people with the same name and date of birth.
    I tried to add a constraint (unique + not null) to 3 fields by running the following code in a MS Access database module:

    Set db = CurrentDb
    s = "ALTER TABLE test ADD CONSTRAINT emp_uk UNIQUE (surname, forename, birth_date );"
    db.Execute (s)

    It worked fine. But I got error msg "Syntax error" when I tried to set NOT NULL to the constraint, no matter the UNIQUE constraint had been deleted ot not:

    ALTER TABLE person ADD CONSTRAINT emp_uk NOT NULL (surname, forename, birth_date );

    How can this problem be solved?

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Is this a clever way of DB design or it can cause problem?

    Well I don't know Access, but in Oracle the syntax would be:

    ALTER TABLE person MODIFY (surname NOT NULL, forename NOT NULL, birth_date NOT NULL );

    or you could say (but I would not):

    ALTER TABLE person ADD CONSTRAINT emp_nn CHECK( surname IS NOT NULL AND forename IS NOT NULL AND birth_date IS NOT NULL);

    Maybe it is similar in Access.

Posting Permissions

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