Does this break normalization

database-designnormalization

I have an order table, and in the order table I have a contact_id.

Now, each contact is linked to an account. For my order table, should I have both contact_id and account_id?

If I wanted to say, list all orders for account ABC, I could just look through all orders where account_id = "ABC's id"

If I had just the contact_id I would have to go through all the contacts and then pull orders based on each contact_id… this seems kind of like a pain in the neck.

I don't know if this is really a normalization question, but the easy way seems a little redundant, but I'm not 100% sure. What is the accepted best practices for doing something like this?

Thanks in advance.

Best Answer

There are different levels of normalization. If your business logic is set up where each contact has an associated account (but more than one contact can be in an account), then there is no reason to put the account ID in the order table because you can get it by going back to the contact table. In other words, account ID in the contact table is a foreign key.

Related Solutions

Mysql – Will this Database design logic work and what should be used

I really have to encourage you to look at the way DB schemas are handled by existing CRM systems and consider whether (1) you wouldn't be further off customizing one of these systems and (2) you're not biting off more than you can chew.

Most CRM systems I've seen have extensive customization capabilities built into them. Typically, this includes the ability to extend and/or modify the definitions of the built-in entities and the ability to add new entity types and relationships. Many of these CRM systems also have the ability to set up some pretty funky security setups, and some might be able to do something similar to what you're suggesting.

I'd also encourage you to try to pry a business scenario out of this client, as well. What's got him convinced that sales reps are going to share information? If they're working together on a team, then existing CRM systems have this covered already. If not, then I'd be a little suspicious about the premise of information sharing. Understand that sales reps are going to follow the money with respect to their behavior. If they're paid to share information, then it'll happen; otherwise, you're going to have to beat them with a stick to share anything.

Sql-server – DB Design Challenge

I would go with your third proposal if contacts do not exist without an entity and to be able to enforce referential integrity.

create table TypeA (
    id int not null identity(1,1)
  , details varchar(64) not null
  , constraint pk_TypeA primary key clustered (id)
  );

create table TypeAContacts (
    typeA_id   int not null
  , contact_id int not null identity(1,1) /* replace this with a good natural key */
  , contact_details varchar(64) not null
  , constraint pk_TypeAContacts primary key clustered (typeA_id, contact_id)
  , constraint fk_TypeAContacts_TypeA foreign key (typeA_id) 
  , constraint uc_TypeAContacts unique(contact_details)
  );

I would go with your fourth proposal if the contacts will always carry the same columns for all entites, and are able to exist without an entity, and to be able to enforce referential integrity.

create table Contacts (
    id int not null identity(1,1)
  , details varchar(64) not null
  , constraint pk_Contacts primary key clustered (id)
  );

create table TypeA (
    id int not null identity(1,1)
  , details varchar(64)
  , constraint pk_TypeA primary key clustered (id)
  );

create table TypeAContacts (
    typeA_id   int not null
  , contact_id int not null
  , constraint pk_TypeAContacts primary key clustered (typeA_id, contact_id)
  , constraint fk_TypeAContacts_TypeA foreign key (typeA_id) 
  , constraint fk_TypeAContacts_Contacts foreign key (contact_id) 
  );

For database design, my favorite books are by Louis Davidson; he has some slide decks and demo code on his website.

Best Answer

Related Solutions

Mysql – Will this Database design logic work and what should be used

Sql-server – DB Design Challenge

Related Question