CODEWITHSUNDEEP
database-design
CodeBender
CodeBender

Reputation: 267

use a foreign key or duplicate table columns?

I have a CONTACT table with columns:

When my system attempt to send a message, it would write a log in another table MSG_LOG with columns:

My question #1: is this a good design? Could this be a better way (below).

MSG_LOG

Question #2: My concern is what if someday we need to expand Contact.name from varchar(50) to varchar(100). What are the chances that we will forget to expand MSG_LOG.contact_id as well?

Any pointers?

Upvotes: 0

Views: 94

Answers (3)

Paul Draper
Paul Draper

Reputation: 83393

A far more important question is: what happens when someone's name or mobile number changes?

With the first design, you retain a record of their name and mobile number at the time the message was sent.

With the second design, you point back to the updated name and number.

(Depending on the history you need, you may even want a third option: contact id, name, and number all in the MSG_LOG.)

Upvotes: 2

Learn Iphone
Learn Iphone

Reputation: 9

Q1. Ans. Don't MobileNo and Name again in MSG_LOG table.

If ContactId become FK then easily you can fetch everything from both table.

So just create MSG_LOG table with

MSGID PK [ it sholud be auto incremented] MSGTEXT sendingdate_time ContactID FK

and If Your messages are repeated the you should create a Messages table also and use ContactId, MessagesId as FK in MSGLOG table and reuse msg and contacts with less information as 3rd normalization. Always save new msg once in msg collections and reuse them.if msg are repeated then you should do it. it is also time saving for front end point of view also.

Upvotes: 0

Paul Evans
Paul Evans

Reputation: 27577

Use a SQL alias type -- in SQL Server it's:

CREATE TYPE name_type FROM varchar(50) not null

Upvotes: 2

Related Questions