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

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?

I use “rel_” for “the related” id.

The id for each of the tables are the primary ids, auto increment and unsigned. The “rel_” ids would be the unsigned foreign keys.

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.

i’m lost again

any chance you could give us the actual SHOW CREATE TABLE results?

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.


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

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

unfortunately, it’s a huge subject, so i would ask you to read the following[list]
[] Optimizing your MySQL Application » SitePoint 10 years old but the concepts are still valid
[
] InformIT: MySQL Query Optimization > Using Indexing nicely written, a chapter from a mysql book
[*] MySQL :: MySQL 5.0 Reference Manual :: 7.5.3 How MySQL Uses Indexes when all else fails, read da manual
[/list]

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

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.

No

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

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!


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

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

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.

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.

  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.

Thanks again for all your time and help!

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.


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

with that PK, you don’t need the additional index on rel_member_id

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?


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

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 –

rel_member_id  num_units_own  num_units_manage
   curly             3              14
   larry             2               9
   larry             3              37
   moe               1              42  

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.


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?