SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27

Thread: database model

  1. #1
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    database model

    I've put together a database model for a rental association's online annual membership registration. There are 2 types of memberships - the first is managers of rental buildings and the other is service providers.

    The managers pay different fees based on the number of units they manage / own (eg: 1-10: $150, 11-20: $200). The service providers pay one flat fee.

    I'm stuck on / keep coming back to having a direct relationship between the member and their fee.

    The relevant tables of the model I've come up with is below. I would appreciate any constructive input or suggestions on whether or not this is practical, and if I'm unnecessarily hung up on the member / fee relationship.

    I am using a PHP framework (Yii) for the first time, and would like to have as good of a database model as possible before taking the next step.

    member
    member_id
    first_name
    last_name
    address
    etc...

    company
    company_id
    company_name
    rel_title_id
    rel_member_id

    manager_fee
    manager_fee_id
    min_num_units
    max_num_units
    manager_fee_amt
    begin_date (effective start date)
    end_date (effective end date)

    service_fee
    service_fee_id
    service_fee_amt
    begin_date (effective start date)
    end_date (effective end date)

    membership
    membership_id
    rel_member_id
    rel_membership_type_id

    membership_type
    membership_type_id
    membership_type_name

    member_num_units_rel
    member_num_units_rel_id
    rel_membership_id
    num_units_manage

    member_payment_rel
    member_payment_rel_id
    rel_membership_id
    payment_amount
    rel_payment_method_id
    payment_date_time

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what does rel_ stand for? for example, why does company have one "rel" title and one "rel" member?

    also, which of all those _id columns is auto_increment, and which is a foreign key?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what does rel_ stand for? for example, why does company have one "rel" title and one "rel" member?
    I use "rel_" for "the related" id.

    Quote Originally Posted by r937 View Post
    also, which of all those _id columns is auto_increment, and which is a foreign key?
    The id for each of the tables are the primary ids, auto increment and unsigned. The "rel_" ids would be the unsigned foreign keys.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that explanation makes sense, but not if i try to apply it to a table like this --

    member_num_units_rel
    member_num_units_rel_id
    rel_membership_id
    num_units_manage

    could you walk me through how that table works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that explanation makes sense, but not if i try to apply it to a table like this --

    member_num_units_rel
    member_num_units_rel_id
    rel_membership_id
    num_units_manage

    could you walk me through how that table works
    This is a relationship table between a member and the number of units they manage. I used the rel_membership_id rather than rel_member_id because it gave me both the membership_type_id and the member_id.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm lost again

    any chance you could give us the actual SHOW CREATE TABLE results?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The 2 _fee tables store the annual fees that would be updated by the administrator, and used by the registration form to charge the appropriate fee.

    Code MySQL:
    CREATE TABLE `member`
    (
      `member_id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT,
      `first_name` varchar(30) NOT NULL,
      `last_name` varchar(30) NOT NULL,
      `address` varchar(40) NOT NULL,
      `city` varchar(30) NOT NULL,
      `province` char(2) NOT NULL,
      `phone` varchar(20) NOT NULL,
      `cell` varchar(12) NOT NULL,
      `fax` varchar(12) NOT NULL,
      `email` varchar(50) NOT NULL,
      `in_directory` tinyint(1) unsigned NOT NULL DEFAULT '0',
      `agree_code_ethics` tinyint(1) unsigned NOT NULL DEFAULT '0',
      `user_name` varchar(10) NOT NULL,
      `password` varchar(30) NOT NULL,
      PRIMARY KEY (`member_id`),
      KEY `member_name` (`first_name`,`last_name`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `company`
    (
      `company_id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT,
      `company_name` varchar(50) NOT NULL,
      `rel_title_id` tinyint(2) unsigned NOT NULL,
      `rel_member_id` mediumint(5) unsigned NOT NULL,
      PRIMARY KEY (`company_id`),
      KEY `member_id` (`rel_member_id`),
      KEY `title_id` (`rel_title_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `title`
    (
      `title_id` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
      `title_name` varchar(30) NOT NULL,
      PRIMARY KEY (`title_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `owner_manager_fee`
    (
      `om_fee_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
      `min_num_units` smallint(3) unsigned NOT NULL,
      `max_num_units` smallint(5) unsigned NOT NULL,
      `om_fee` decimal(6,2) unsigned NOT NULL,
      `begin_date` date NOT NULL,
      `end_date` date NOT NULL,
      PRIMARY KEY (`om_fee_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `service_fee`
    (
      `service_fee_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
      `s_fee` decimal(6,2) unsigned NOT NULL,
      `begin_date` date NOT NULL,
      `end_date` date NOT NULL,
      PRIMARY KEY (`service_fee_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `membership_type`
    (
      `membership_type_id` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
      `membership_type_name` varchar(30) NOT NULL,
      PRIMARY KEY (`membership_type_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `membership`
    (
      `membership_id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT,
      `rel_member_id` mediumint(5) unsigned NOT NULL,
      `rel_membership_type_id` tinyint(2) unsigned NOT NULL,
      PRIMARY KEY (`membership_id`),
      KEY `member_id` (`rel_member_id`),
      KEY `membership_type_id` (`rel_membership_type_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `member_num_units_rel`
    (
      `member_num_units_rel_id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT,
      `rel_membership_id` mediumint(5) unsigned NOT NULL,
      `num_units_own` smallint(5) unsigned NOT NULL,
      `num_units_manage` smallint(5) unsigned NOT NULL,
      PRIMARY KEY (`member_num_units_rel_id`),
      KEY `membership_id` (`rel_membership_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `member_payment_rel`
    (
      `member_payment_rel_id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT,
      `rel_membership_id` mediumint(5) unsigned NOT NULL,
      `rel_payment_method_id` tinyint(2) unsigned NOT NULL,
      `payment_amount` decimal(6,2) unsigned NOT NULL,
      `payment_date_time` datetime NOT NULL,
      `payment_reference_num` varchar(30) NOT NULL,
      PRIMARY KEY (`member_payment_rel_id`),
      KEY `membership_id` (`rel_membership_id`),
      KEY `payment_method_id` (`rel_payment_method_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    in general, those tables look fine

    i noticed that each company can have only one member... you might want to allow the database to accommodate more than one, even if you don't think you'll need it

    i wouldn't restrict titles to predetermined values, i would just let each member have a free-form title column without relating it to a titles table

    the membership table appears to me to simply associate a member with a membership type, which is the classic many-to-many structure (without the auto_increment, which is unnecessary here)

    however, as far as i can tell you don't want a given member to have more than one membership type, right? so i would probably put membership type directly into the members table and dispense with the membership table

    this gets us down to the last two tables

    the member_num_units_rel table simply assigns values for units owned and managed to a specific "rel_membership_id" which is really just the member_id plus the type, while the member_payment_rel table assigns payment details to each "rel_membership_id"

    so the essential question seems to be: can a given member have more than one type? can a member be both a manager of rental buildings and a service provider?

    i suspect the answer will be no, and you can collapse and simplify several of these tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy.

    I've spoken again to the client and have some updated info.

    A large management company would be a member and can have more than one contact which will be in the member table. In this situation the member records would be contacts, and the membership type could only be associated with one of those contacts' details as the main contact.

    There are also individual members (landlords) not requiring company details that may manage/own one or two units.

    Here's what I've done:
    - deleted the membership table and added the rel_membership_type_id field to the member table. For the member record that is more for contact purposes, the rel_membership_type_id field will still contain its value in case the "main contact" changes. I also added a field called "primary" so I know which is the main contact. If the main contact changes the member_num_units_rel and member_payment_rel will be affected, depending on if it is a change or deletion.

    - since a member may or may not have a company name, or a member can only be related to one company, or one company can have more than one member, I created a member_company_rel table with rel_member_id, rel_company_id and title fields and eliminated the title table.
    If a member has a more than one company relationship, there will need to be another record created in the member table. This is mainly because of the payment and num_units relationship tables, which are linked to the member_id

    - member_payment_rel table is now related to the member table directly, and I kept it since there will be many payment details (yearly fee) to one member, and no payment details for the member records that are for contact purposes

    - member_num_units_rel table is now related to the member table directly as well, and I kept it since there may be one set of or no units associated with a member


    Quote Originally Posted by r937 View Post
    the membership table appears to me to simply associate a member with a membership type, which is the classic many-to-many structure (without the auto_increment, which is unnecessary here)
    The newly added member_company_rel table is essentially the same, 2 foreign key id fields with the title varchar field. Would I not need the primary id key for this table or just not require the auto_increment? Why for either? I thought there needed to be a unique id field.


    Quote Originally Posted by r937 View Post
    can a given member have more than one type? can a member be both a manager of rental buildings and a service provider?
    No

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by spiderling View Post
    The newly added member_company_rel table is essentially the same, 2 foreign key id fields with the title varchar field. Would I not need the primary id key for this table or just not require the auto_increment? Why for either? I thought there needed to be a unique id field.
    if you're thinking that every table needs to have an auto_increment id primary key, this is, sorry to say, wrong

    other than that, i can't say much, as i can't understand your new table design without seeing the SHOW CREATE TABLE for it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if you're thinking that every table needs to have an auto_increment id primary key, this is, sorry to say, wrong
    Would the auto_increment id primary key not be required then due to the foreign keys? Can you please briefly explain the guideline or best practice? It's disappointing when you think you're doing something the right way for so long, and then you find out otherwise. I'm going to research this some more. Thanks for the info and help!


    Quote Originally Posted by r937 View Post
    other than that, i can't say much, as i can't understand your new table design without seeing the SHOW CREATE TABLE for it
    Code MySQL:
    CREATE TABLE `member`
    (
      `member_id` MEDIUMINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
      `first_name` VARCHAR(30) NOT NULL,
      `last_name` VARCHAR(30) NOT NULL,
      `address` VARCHAR(40) NOT NULL,
      `city` VARCHAR(30) NOT NULL,
      `province` CHAR(2) NOT NULL,
      `phone` VARCHAR(20) NOT NULL,
      `cell` VARCHAR(12) NOT NULL,
      `fax` VARCHAR(12) NOT NULL,
      `email` VARCHAR(50) NOT NULL,
      `in_directory` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
      `agree_code_ethics` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
      `user_name` VARCHAR(10) NOT NULL,
      `password` VARCHAR(30) NOT NULL,
      `rel_membership_type_id` TINYINT(2) UNSIGNED NOT NULL,
      `primary` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
      PRIMARY KEY (`member_id`),
      KEY `member_name` (`first_name`,`last_name`)
      KEY `membership_type_id` (`rel_membership_type_id`),
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `company`
    (
      `company_id` MEDIUMINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
      `company_name` VARCHAR(50) NOT NULL,
      PRIMARY KEY (`company_id`),
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `member_company_rel`
    (
      `member_company_rel_id` MEDIUMINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
      `rel_member_id` MEDIUMINT(5) UNSIGNED NOT NULL,
      `rel_company_id` MEDIUMINT(5) UNSIGNED NOT NULL,
      `title` VARCHAR(30) NOT NULL,
      PRIMARY KEY (`company_id`),
      KEY `member_id` (`rel_member_id`),
      KEY `company_id` (`rel_company_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `owner_manager_fee`
    (
      `om_fee_id` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
      `min_num_units` SMALLINT(3) UNSIGNED NOT NULL,
      `max_num_units` SMALLINT(5) UNSIGNED NOT NULL,
      `om_fee` DECIMAL(6,2) UNSIGNED NOT NULL,
      `begin_date` DATE NOT NULL,
      `end_date` DATE NOT NULL,
      PRIMARY KEY (`om_fee_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `service_fee`
    (
      `service_fee_id` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
      `s_fee` DECIMAL(6,2) UNSIGNED NOT NULL,
      `begin_date` DATE NOT NULL,
      `end_date` DATE NOT NULL,
      PRIMARY KEY (`service_fee_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `membership_type`
    (
      `membership_type_id` TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
      `membership_type_name` VARCHAR(30) NOT NULL,
      PRIMARY KEY (`membership_type_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `member_num_units_rel`
    (
      `member_num_units_rel_id` MEDIUMINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
      `rel_member_id` MEDIUMINT(5) UNSIGNED NOT NULL,
      `num_units_own` SMALLINT(5) UNSIGNED NOT NULL,
      `num_units_manage` SMALLINT(5) UNSIGNED NOT NULL,
      PRIMARY KEY (`member_num_units_rel_id`),
      KEY `member_id` (`rel_member_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `member_payment_rel`
    (
      `member_payment_rel_id` MEDIUMINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
      `rel_member_id` MEDIUMINT(5) UNSIGNED NOT NULL,
      `rel_payment_method_id` TINYINT(2) UNSIGNED NOT NULL,
      `payment_amount` DECIMAL(6,2) UNSIGNED NOT NULL,
      `payment_date_time` DATETIME NOT NULL,
      `payment_reference_num` VARCHAR(30) NOT NULL,
      PRIMARY KEY (`member_payment_rel_id`),
      KEY `member_id` (`rel_member_id`),
      KEY `payment_method_id` (`rel_payment_method_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by spiderling View Post
    Can you please briefly explain the guideline or best practice?
    an auto_increment primary key is one example (by far the most ubiquitous; another is a UUID) of a surrogate key

    as the name suggests, this is a key that is used in place of some other key as the primary key

    any column, or set of columns, that uniquely identifies a single row in a table is called a candidate key (obviously an auto_increment fits this description), and one of the table's candidate keys is then selected as the primary key

    obviously, a surrogate key like an auto_increment is extremely useful when the only other candidate key in a table is some ungainly combination of other columns

    remember, candidate key means uniqueness

    consider what combinations of columns you would have to designate as a candidate key for your members table (i should warn you that designing a key for real-world entities like people or places is maddeningly difficult, and virtually everyone uses a surrogate)

    unfortunately, if you use an auto_increment but overlook declaring a UNIQUE constraint on the other candidate key, you run the risk of having "duplicate" rows in your table (they aren't completely duplicated, because they'll have different auto_increment numbers, even if every other column is identical)

    now in the case of a relationship table, in which you have two foreign keys, this pair of columns is definitely a candidate key (because you want the combination to be unique, right? you wouldn't want to assign the same member to the same company more than once, right?), and therefore they make an ideal primary key

    so this is sub-optimal --

    CREATE TABLE member_company_rel
    ( member_company_rel_id MEDIUMINT(6) UNSIGNED NOT NULL AUTO_INCREMENT
    , rel_member_id MEDIUMINT(5) UNSIGNED NOT NULL
    , rel_company_id MEDIUMINT(5) UNSIGNED NOT NULL
    , title VARCHAR(30) NOT NULL
    , PRIMARY KEY (company_id) -- error; you meant member_company_rel_id
    , KEY member_id (rel_member_id) -- index only
    , KEY company_id (rel_company_id) -- index only
    )

    note that "KEY" in mysql syntax is equivalent to INDEX, and has nothing to do with uniqueness -- only the PRIMARY KEY is unique by default, and it also gets an index by default

    this would be better --

    CREATE TABLE member_company_rel
    ( rel_member_id MEDIUMINT(5) UNSIGNED NOT NULL
    , rel_company_id MEDIUMINT(5) UNSIGNED NOT NULL
    , title VARCHAR(30) NOT NULL
    , PRIMARY KEY ( rel_member_id, rel_company_id )
    , INDEX company_member ( rel_company_id, rel_member_id )
    )

    make sense? we can talk about the INDEX later, if you wish

    but with this primary key, you cannot add the same member to the same company more than once
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks very much! I've been absorbing the info reading over it a couple of times or so.

    Just verifying that my interpretation and understanding is correct.
    I can go without using an auto increment primary key id in any relationship table if I have 2 (or more?) unique columns that exist (not necessarily id columns) and the combination of the unique columns needs to be unique to prevent duplicate data / combination.

    Quote Originally Posted by r937 View Post
    note that "KEY" in mysql syntax is equivalent to INDEX, and has nothing to do with uniqueness
    I am seeing this KEY, INDEX equivalence with the SHOW CREATE TABLE for the 'member_name' INDEX I created for the member table. It shows KEY instead of INDEX.
    Code MySQL:
      KEY `member_name` (`first_name`,`last_name`),
    I've also noticed the index method BTREE and the index type of Normal.

    I've read a little about INDEX, but if it's not too much trouble to explain, I would appreciate it.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    unfortunately, it's a huge subject, so i would ask you to read the following
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again for all your time and help!

  16. #16
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just getting ready to implement this, and I had thought regarding the member_num_units_rel table. This should not and will not have duplicate rows since the member cannot be duplicated in a company. Since that is the case I changed this relationship table by removing the auto increment primary key, and using the 3 columns as the primary key.
    Code MySQL:
    CREATE TABLE `mrm_member_num_units_rel`
    (
      `rel_member_id` MEDIUMINT(5) UNSIGNED NOT NULL,
      `num_units_own` SMALLINT(5) UNSIGNED NOT NULL,
      `num_units_manage` SMALLINT(5) UNSIGNED NOT NULL,
      PRIMARY KEY (`rel_member_id`,`num_units_own`,`num_units_manage`),
      KEY `member_id` (`rel_member_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    with that PK, you don't need the additional index on rel_member_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.

    Why is the KEY for member_company_rel kept and not member_num_units_rel? Is it because two fields are being used as opposed to the one that was being used?

    Code MySQL:
    CREATE TABLE `mrm_member_company_rel`
    (
      `rel_member_id` MEDIUMINT(5) UNSIGNED NOT NULL,
      `rel_company_id` MEDIUMINT(5) UNSIGNED NOT NULL,
      `title` VARCHAR(30) NOT NULL,
      PRIMARY KEY (`rel_member_id`,`rel_company_id`),
      KEY `member_company` (`rel_member_id`,`rel_company_id`) 
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
    CREATE TABLE `mrm_member_num_units_rel`
    (
      `rel_member_id` MEDIUMINT(5) UNSIGNED NOT NULL,
      `num_units_own` SMALLINT(5) UNSIGNED NOT NULL,
      `num_units_manage` SMALLINT(5) UNSIGNED NOT NULL,
      PRIMARY KEY (`rel_member_id`,`num_units_own`,`num_units_manage`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm not sure i understand the question

    however, in the second table you posted -- and i should have spotted this yesterday -- it would seem that you really meant to have just one column as the PK, rather than three

    with three, you will eventually have the following scenario --
    Code:
    rel_member_id  num_units_own  num_units_manage
       curly             3              14
       larry             2               9
       larry             3              37
       moe               1              42
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since a company cannot have duplicate members then the member_num_units_rel table can't have a duplicate row.

    If Larry was in the member_num_units_rel table more than once it would be because he is the member of more than one company. He would then have a different member_id.

    Code:
    rel_member_id  num_units_own  num_units_manage
       1 (curly)             3              14
       2 (larry)             2               9
       4 (larry)             3              37
       3 (moe)               1              42
    Can I still use the 3 fields as the PK in the member_num_units_rel table? Do I add the rel_member_id FOREIGN KEY back? If not, why is the KEY for the member_company_rel table kept and not the member_num_units_rel table? Is it because two fields are being used for the KEY in the member_company_rel table as opposed to the one being used for the KEY in the member_num_units_rel table?

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by spiderling View Post
    He would then have a different member_id.
    ouch!!

    Code:
    rel_member_id  num_units_own  num_units_manage
       1 (curly)             3              14
       2 (larry)             2               9
       4 (larry)             3              37
       3 (moe)               1              42
    Quote Originally Posted by spiderling View Post
    Can I still use the 3 fields as the PK in the member_num_units_rel table?
    i wouldn't, because what you want is one row per member, but with a 3-column PK you could have more than one

    okay, you said he'd have two different member ids, but the point is, you should let the database ensure uniqueness, rather than relying on application code

    Quote Originally Posted by spiderling View Post
    Do I add the rel_member_id FOREIGN KEY back?
    yes, because you don't want any rows in here for member_id values that do not exist in the members table

    right?

    Quote Originally Posted by spiderling View Post
    If not, why is the KEY for the member_company_rel table kept and not the member_num_units_rel table?
    KEY means INDEX -- it is a physical structure that allows for performance optimization

    unfortunately mysql uses both KEY and INDEX to mean the same thing, when it should allow only INDEX

    FOREIGN KEY and PRIMARY KEY are not at all the same as KEY -- with the exception that a PRIMARY KEY automatically gets an INDEX, and so does a FOREIGN KEY after version something-or-other (you used to have to declare the INDEX for a FOREIGN KEY yourself)

    now i'm sure after all these posts we are both thoroughly confused, so if you need further help, i would suggest you do your SHOW CREATE TABLEs again, as i know i am already half lost
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i wouldn't, because what you want is one row per member, but with a 3-column PK you could have more than one

    okay, you said he'd have two different member ids, but the point is, you should let the database ensure uniqueness, rather than relying on application code
    I confused myself thinking that the combination of the 3 fields would create a unique value together and prevent duplicates. Thinking about it again proves that wrong, because an insert can still be done with the same member_id so long as one of the other values is different. I decided to make the PK the rel_member_id as well as making it the FK.
    Code MySQL:
    CREATE TABLE `mrm_member_num_units_rel`
    (
      `rel_member_id` MEDIUMINT(5) UNSIGNED NOT NULL,
      `num_units_own` SMALLINT(5) UNSIGNED NOT NULL,
      `num_units_manage` SMALLINT(5) UNSIGNED NOT NULL,
      PRIMARY KEY (`rel_member_id`),
      KEY (`rel_member_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by spiderling View Post
    I decided to make the PK the rel_member_id as well as making it the FK.
    well, what you posted doesn't actually have a FOREIGN KEY

    plus, your KEY -- which is the equivalent of INDEX -- is actually redundant

    Code:
      PRIMARY KEY (`rel_member_id`),
      KEY (`rel_member_id`)
    a PRIMARY KEY gets an index by default

    when you declare this KEY, which is an index, it is actually redundant

    KEY is ~not~ the same as FOREIGN KEY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    well, what you posted doesn't actually have a FOREIGN KEY

    plus, your KEY -- which is the equivalent of INDEX -- is actually redundant

    Code:
      PRIMARY KEY (`rel_member_id`),
      KEY (`rel_member_id`)
    a PRIMARY KEY gets an index by default

    when you declare this KEY, which is an index, it is actually redundant

    KEY is ~not~ the same as FOREIGN KEY
    The desktop software (Navicat for MySQL) I am using shows it as KEY. When I create the table, under the Foreign Keys tab I set the rel_member_id as a foreign key. However, when I use SHOW CREATE TABLE, it shows as KEY.

    Aside from it showing as KEY, can I set rel_member_id as a foreign key, or do I even need to?

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by spiderling View Post
    However, when I use SHOW CREATE TABLE, it shows as KEY.
    there is a word for this type of result, and that word is "wrong"

    Quote Originally Posted by spiderling View Post
    can I set rel_member_id as a foreign key, or do I even need to?
    yes you can and yes you should

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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