Hi,

I have the following tables:

CREATE TABLE [tblGeography] (
[ZoneID] Int,
[ZoneName] [char] (10),
[StateID] Int,
[StateName] [char] (35),
[CityID] Int,
[CityName] [char] (50)
)


CREATE TABLE [tblCustomers] (
[CustCode] [varchar] (20) ,
[CustName] [varchar] (64),
[CityID] Int
)


The logic is as follows:

Zone

What I want to do is "flatten" my data. I am thinking about creating a
view as follows:

1) Add the tables listed above
2) Link the CityID fields between the Two Tables
3) Add all fields from Customer table EXCEPT for the CITYID field
4) Add all Name fields (ZoneName, StateName, CityName) from Geography
table

Is my approach correct and will it yield the correct and desired
results? The reason for asking is because I don't recall exactly what I
did but while trying this out I got a much higher count(*) of customers
than the actual number of customers... I must have done something wrong


Anyway...I'll appreciate your help.

--
Thanks.

Learner
Why not just to have each table for the data?
CREATE TABLE Zones
(
ZoneId INT NOT NULL PRIMARY KEY,
ZoneName VARCHAR(100) NOT NULL
)
CREATE TABLE States
(
StateId INT NOT NULL PRIMARY KEY,
StateName VARCHAR(100) NOT NULL
)
............

CREATE TABLE Customer_Details
(
CustCode INT NOT NULL REFERNCES Customers (CustCode ),
ZoneId INT NOT NULL REFERNCES Zones(ZoneId ),
StateId INT NOT NULL REFERNCES States(StateId ),..........
.........
CONSTRAINT uid_PK PRIMARY KEY (CustCode ,ZoneId .......) Modify it for your
needs
)

"Learner" <wantnospam (AT) email (DOT) com> wrote

thanks for the reply... assuming that I go with your approach (which I
understand and agree) this would still lead me to my original question
regarding the view. The data I am referring to is coming in from on OLTP
(to be used for a data mart) terefore I did not focus too much on the
normalization part.

Will appreciate your reply (assuming my question is clear)??

Thanks & Regards.

--

--
Thanks.