Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Question Clarification on DB design...

    Hi Folks,

    I'm writing an application and kind of wish I paid more attention at University on database design I think I've got it right, but the ERM diagram that MySQL Workbench has created from my table create script has confused me a bit.

    This is an example of the problem I'm having:

    I have a table called person, the person can have multiple names, e.g. first, last, middle etc. Within my application I'll control the display by a field called name_order. If you delete the person then all the names for that person will be deleted.

    Here are the two tables:

    CREATE TABLE data_name(
    person_id INT UNSIGNED NOT NULL,
    name VARCHAR(32),
    name_order INT UNSIGNED,
    PRIMARY KEY (person_id),
    UNIQUE INDEX (name)
    ) ENGINE=InnoDB;

    CREATE TABLE data_person(
    person_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title_id INT UNSIGNED,
    date_of_birth DATE,
    PRIMARY KEY (person_id),
    FOREIGN KEY (person_id) REFERENCES data_name(person_id) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB;

    I was under the impression that this is a one to many relationship. However, the ERM thats been generated is displayed like this:

    Code:
                        /
    data_name ||---------- data_person
                        \
    To me, this means that one name could belong to many persons.

    Can someone clarify this for me please?

    Thanks,

    Andy

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Quote Originally Posted by AndyMcCall View Post
    To me, this means that one name could belong to many persons.

    ...

    Andy
    And isn't this the truth? Surely you're not the only Andy in the world.

    However, your model doesn't look right. The constraints on DATA_NAME seem incorrect, and the foreign key seems to be inverted, pointing in the wrong direction.
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

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