SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)

    Making Model needlessly complex or better design?

    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:
    Code:
     
    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:
    Code:
     
    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:
    Code:
     
    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:
    Code:
     
    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:
    Code:
     
    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:
    Code:
     
    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
    ictus==""

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    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:

    Code:
    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
    Code:
    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
    Code:
    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
    ictus==""

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ServerStorm View Post
    The address_type_id now sepecifies if it is home, company, or po_box.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    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
    ictus==""

  6. #6
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    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
    ictus==""

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thanks steve, very kind of you
    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
  •