Normalization

table normalization

Page 1 of 1

10 Replies - 2340 Views - Last Post: 11 April 2007 - 08:59 PM Rate Topic: -----

#1 rubyjoeseph  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 25-February 07

Normalization

Posted 06 March 2007 - 08:22 PM

HELP ME......... I have databases named AMBULANCE, HOSPITAL, PHYSICIAN, BLOOD BANK for all these tables there are some common fields like country, state,city, area........ So i have created separate tables for each field...... H ow can i normalize these tables...... :pirate:
Is This A Good Question/Topic? 0
  • +

Replies To: Normalization

#2 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1865
  • View blog
  • Posts: 20,278
  • Joined: 17-March 01

Re: Normalization

Posted 06 March 2007 - 08:41 PM

Gonna need more info. Can you show us the actual table structures with all the columns.

Are you just supposed to "normalize" them, or do you have to normalize to 3rd normal form, 4th normal form, etc. ??
Was This Post Helpful? 0
  • +
  • -

#3 rubyjoeseph  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 25-February 07

Re: Normalization

Posted 06 March 2007 - 09:16 PM

View Postskyhawk133, on 6 Mar, 2007 - 08:41 PM, said:

Gonna need more info. Can you show us the actual table structures with all the columns.

Are you just supposed to "normalize" them, or do you have to normalize to 3rd normal form, 4th normal form, etc. ??

Was This Post Helpful? 0
  • +
  • -

#4 rubyjoeseph  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 25-February 07

Re: Normalization

Posted 06 March 2007 - 09:23 PM

i just Wanna normalize my tables...
BLDAMB
service id
service name
area
city
state
country
phone
type

PHYSICIAN
did
dname
specilist
address
area
city
state
country
phone
hospname

HOSPITAL
hid
hanme
specialist
address
area
city
state
country
phone

THESE ARE THE TABLES I AM USING....... Now i have confused how can i normalize these tables please help me......
Was This Post Helpful? 0
  • +
  • -

#5 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: Normalization

Posted 07 March 2007 - 09:02 AM

a common way to do that is to put the common data in a seperate table, lets call is Adress:
ID
specialist
address
area
city
state
country
phone

The id is a sequence.

Then, in all those tables where that data was originaly part of the fields list, you remove them, and include one new field: AdressID
That new field links them to the right information in the Adress table.
Was This Post Helpful? 0
  • +
  • -

#6 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Normalization

Posted 07 March 2007 - 10:20 PM

What is the relation between your tables?

Do all PHYSICIAN work at the HOSPITAL?

Is there any relation between BLDAMB and the other two tables?

If all the addresses relate to the HOPSPITAL then I would store the address in that table. Then link the other two tables through a foreign key to that table.

Specialist probably refers to just a type of PHYSICIAN, so I would keep that data stored in the PHYSICIAN table.

Basically you do not need to store the same information in more than one table if there is some relation to the data and other tables.

Once you eliminate redundancy(duplicate information) and relate the tables using primary and foreign keys you will be in 2NF.
Was This Post Helpful? 0
  • +
  • -

#7 DilutedImage  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 14
  • View blog
  • Posts: 646
  • Joined: 20-November 06

Re: Normalization

Posted 24 March 2007 - 10:16 AM

I'm a bit late on the response, but for those interested, here's an excellent write-up on database normalization:
Intro to Data Modeling - Normalization
Intro to Data Modeling - Advanced Normalization

2
Was This Post Helpful? 0
  • +
  • -

#8 MemeRot  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 11-April 07

Re: Normalization

Posted 11 April 2007 - 11:33 AM

Pulling every address out to another table is the worst fucking idea I have ever heard. How much is storage costing you to have all those different entities have separate addresses? $.02 maybe. How many long nights will you be struggling to maintain this later on when doctors move from one hospital to another, or to private practice, etc? MANY. Maintainability is more important than strict normalization. Performance is more important than strict normalization. Oh I forgot to mention the clusterfuck that dealing with international addresses would introduce if you attempted to normalize for US addresses and later on have to deal with overseas addresses. Don't complicate this. Leave it as is.
Was This Post Helpful? 0
  • +
  • -

#9 DilutedImage  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 14
  • View blog
  • Posts: 646
  • Joined: 20-November 06

Re: Normalization

Posted 11 April 2007 - 08:47 PM

Wow. You clearly do not understand the concept of normalization. Normalization is a SOLUTION to the various problems you've stated. .. While I can appreciate the passion expressed in your response, I respectfully request that you save it for a topic within your knowledge-base. Perhaps you could even consider actually utilizing such a process, so that you've got some experience to speak upon.

2
Was This Post Helpful? 0
  • +
  • -

#10 DilutedImage  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 14
  • View blog
  • Posts: 646
  • Joined: 20-November 06

Re: Normalization

Posted 11 April 2007 - 08:53 PM

This is another great writeup on databases. Though it doesn't specifically address the topic of normalization, it's an excellent introduction to the concept of relational databases:

Kirupa - Relational Database Design
Was This Post Helpful? 0
  • +
  • -

#11 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1865
  • View blog
  • Posts: 20,278
  • Joined: 17-March 01

Re: Normalization

Posted 11 April 2007 - 08:59 PM

I agree with trogdor, DilutedImage, and Jayman. Having the address in each table is redundant and they should be broken down more.

I would add a location table and store any location information in there. Since phone numbers could easily be doctor specific, not location specific, I would leave the phone column in the individual tables. But for addresses and the like, moving them in to a locations table would allow you to easily assign doctors to hospital, a hospital to a location, a service to a location, etc.

You have to decide exactly what your relationships are. Will 1 doctor belong to 1 hospital. Will 1 hospital have 1 location. Will Many doctors belong to 1 hospital. Will 1 doctor belong to many hospitals. Can 1 service belong to many locations.

Based on these decisions, you may need to create "intersection" tables to do many to many relationships in a normalized fashion.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1