Making Model needlessly complex or better design?

ServerStorm · July 20, 2011, 3:38pm

Hi

I am currently designing a database that had employees, addresses, corporate and residential users. Here are some of scenarios:

a user can belong to multiple (many) companies
a user can belong to several (many) residences
a user can be an employee
residential addresses dont have a name they have only addresses
coporation have distinct categorizations on how they are broken down i.e. departments, buildings, floor, post office box …
all types of users have multiple forms of communications ie. land phone, cell phone, email, messaging…

So do you think that this is a needlessly complex design or appropriate?

Any of the NULL fields can be left blank; the communications table will almost always have some of the fields blank.
The company is currently linked as a lookup from the addresses table (a bit cludgy do you think?)
a mapping table user2communications2addresses brings all the related data together and I think fufills all of the many to many relationships that I refered to above?

Addresses:

 
CREATE TABLE  `users`.`addresses` (
  `address_id` int(11) NOT NULL,
  `address_type_id` int(11) NOT NULL,
  `intended_use` varchar(150) default NULL,
  `street_number` int(11)  NOT NULL,
  `street_name` varchar(150) NOT NULL,
  `street_direction` varchar(2) default NULL,
  `post_office_box` int(1) default NULL,
  `locality` varchar(20) default NULL,     
  `city` varchar(100) NOT NULL,
  `province` varchar(100) NOT NULL,
  `post_code` varchar(20) NOT NULL,
  `region` varchar(150) default NULL,
  `company_id` int(11) default NULL,
  `assembled address block` text,
  PRIMARY KEY  (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Companies:

 
CREATE TABLE  `users`.`companies` (
  `company_id` int(11)  NOT NULL,
  `company_name` varchar(150)  NOT NULL,
   `department_name` varchar(100)  NOT NULL,
   `building` varchar(100)  NOT NULL,
   `floor` varchar(100) default NULL,
   `mail_stop` varchar(100) default NULL,
  PRIMARY KEY  (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Users:

 
CREATE TABLE  `users`.`users` (
  `uid_number` int(11) NOT NULL,
  `uid` varchar(128) NOT NULL,
  `password` char(64) NOT NULL,
  `salutation` varchar(6) default NULL,
  `first_name` varchar(150) default NULL,
  `middle_names` varchar(250) default NULL,
  `last_name` varchar(150) default NULL,
  `language` varchar(100) default NULL,
  `written_greeting` varchar(150) default NULL,
  `verbal_greeting` varchar(150) default NULL,
  `degree` varchar(150) NOT NULL COMMENT 'such as M.S., Ph. D, and other honorary titles',
  PRIMARY KEY  USING BTREE (`uid_number`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

communication_types:

 
CREATE TABLE  `users`.`communication_types` (
  `communication_type_id` tinyint(4) NOT NULL auto_increment,
  `communication_type` varchar(150) NOT NULL,
  `communication_category` varchar(150) NOT NULL default 'phone',
  PRIMARY KEY  USING BTREE (`communication_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

communications:

 
CREATE TABLE  `users`.`communications` (
  `communication_id` int(11) NOT NULL default '0',
  `communication_type_id` tinyint(4) NOT NULL default '0',
  `phone_number` int(11) default NULL,
  `internal_extension` smallint(6) default NULL,
  `secondary_dialing_instructions` text,
  `complete_foreign_dialing_sequence` text,
  `complete_local_dialing_sequence` text,
  `text_handle` varchar(250) default NULL,
  `email_address` varchar(250) default NULL,
  `is_primary_email_address` tinyint(1) default NULL,
  `url` varchar(250) default NULL,
  PRIMARY KEY  USING BTREE (`communication_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

user2communications2addresses:

 
CREATE TABLE  `users`.`user2communications2addresses` (
  `uid_number` int(11) NOT NULL,
  `communication_id` int(11) NOT NULL,
  `addresses_id` int(11) NOT NULL,
  PRIMARY KEY  USING BTREE (`uid_number`,`communication_id`,`addresses_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Your thoughts on this design are appreciated.

Regards,
Steve

r937 · July 20, 2011, 8:28pm

appropriate? only you can say, because only you know the application’s business requirements

but let me give you a strategy to decide a few things

does the app care about an address that doesn’t have a company? or will you, when a company moves from one address to another, delete the address row that the company used to belong to?

in my experience, an address is hardly ever an entity of interest, and almost always just a plain old data attribute of some other entity (e.g. person or business)

for a user’s cell phone, it looks like you relate the user to a communication via an address, and i’m wondering which address you use for that

ServerStorm · July 20, 2011, 9:56pm

Hi Rudy,

Yes I thought this might be a little too open ended although not having designed more than 6 databases I sometimes question much of what I do.

To answer your questions:

does the app care about an address that doesn’t have a company?
Yes as an address that doesn’t have a company is a home address.

In my experience, an address is hardly ever an entity of interest, and almost always just a plain old data attribute of some other entity (e.g. person or business)

In this case the db is for a marketing company and the addresses are important but you are correct it is an attribute of some other entity.

One of the reasons I designed it this way is a user can belong to serveral different companies (part-time jobs, contractors…) by relating the users to multiple addresses we can see that the users are distinct and and not ‘different’ users; therefore our client won’t market to them 2 or three times.

for a user’s cell phone, it looks like you relate the user to a communication via an address, and i’m wondering which address you use for that
Yup that is a bad call, so I have altered that to:

CREATE TABLE  `users`.`user2addresses` (
  `uid_number` int(11) NOT NULL,
  `address_id` int(11) NOT NULL,
  PRIMARY KEY  (`uid_number`,`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and

CREATE TABLE  `users`.`user2communications` (
`uid_number` int(11) NOT NULL,
`communication_id` int(11) NOT NULL,
PRIMARY KEY  USING BTREE (`uid_number`,`communication_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This seems to better align itself with the concept of an address being part of an entity.

In the addresses table

CREATE TABLE  `users`.`addresses` (
`address_id` int(11) NOT NULL,
`address_type_id` int(11) NOT NULL,
`intended_use` varchar(150) default NULL,
`street_number` int(11) NOT NULL,
 `street_name` varchar(150) NOT NULL,
 `street_direction` varchar(2) default NULL,
`post_office_box` int(1) default NULL,
`locality` varchar(20) default NULL,
`city` varchar(100) NOT NULL,
`province` varchar(100) NOT NULL,
 `post_code` varchar(20) NOT NULL,
`region` varchar(150) default NULL,
`country_iso_code` char(2) NOT NULL,
  `company_id` int(11) default NULL,
`assembled address block` text,
PRIMARY KEY  (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The address_type_id now sepecifies if it is home, company, or po_box.I guess I am going to go with this design as it seems the requirement have lead me this way and if it is difficult, I will learn from it

Thanks for your ideas on this.

Regards,
Steve

r937 · July 20, 2011, 10:32pm

i don’t like this, either

can a company not have a p.o. box address?

also, you said “a user can belong to serveral different companies” but you’ve got company_id embedded in the uiser table which restricts it to one

i’ve always found that if you try to do conceptual entity-relationship modelling using CREATE TABLE statements, you get too quickly embroiled in details and miss the forest for all the trees

use ER modelling software, or better yet, paper and pencil, untill you’ve got the cardinalities nailed down, e.g. user belongs to more than one company

ServerStorm · July 21, 2011, 2:59pm

Hi Rudy,

i’ve always found that if you try to do conceptual entity-relationship modelling using CREATE TABLE statements, you get too quickly embroiled in details and miss the forest for all the trees…use ER modelling software, or better yet, paper and pencil, untill you’ve got the cardinalities nailed down, e.g. user belongs to more than one company
I have taken your advice on this as that is exactly what was happening. I am sure it will help. Anyway I like drawing better

Coincidentally, doing this means that the references to company and address types don’t belong in the user or address tables. Now a singe users2addresses map user, to address, to address_type. Now a user can have a home address, a home office address, a company address, and a PO box.

Thank you for your help and (hopefully) getting me on a better track

Regards,
Steve

ServerStorm · July 21, 2011, 3:36pm

Not that it is too big of a deal, but I figure I always like how your write and what you say so I picked up the printed/ebundle edition of you Simply SQL book. I figure the amount that you have helped me over the years and the little that I can do to help others in terms of SQL seems out of balance so this at least feels better.

Regards,
Steve

r937 · July 21, 2011, 4:14pm

thanks steve, very kind of you