Re: Data Model - Please Comment on my Approach

Quoting unnamed sources, Linda claimed:

> Helo,
>
> I have just replaced someone and inherited a number of database that are
> not very well structured. I would like your comments/suggestion on how to
> improve the schema.
>
> My team has a list of postcodes in an access database. The codes start
> from 1011AA. A table stores the following Social Demographic information
> is stored about each Six Character (1011AA) postcode:
>
> 1.The total number of households
> 2.Total Number of 1 person households
> 3. Total Number of 2 person households
> 3.Total Number of 3 person households
> 4.Total Number of 4 person households
> 5.Total Number of 5 person households
> 6. Total Number of 6 or more person households
> 7.Total Average Head of Household < 25
> 8.Total Average Head of Household 25 -29
> 9.Total Average Head of Household 30-34
> 10.Total Average Head of Household 35-39
> 11. Incone < 13, 500
> 12. Income 13500- 20500
> 13. House Type Apartment
> 14. House Type Terrace House
> 15. House Type Semi-Detached
> 16. House Type Farm
> ...
> etc, etc
> There are several tables with Social Economic Daata, Purchasing power,
> etc, etc, that have the same format. As you can see above, the field names
> contain reserved symbols,, numerals, spaces which is naughty, etc, etc,
> but that a different story altogether.
>
> The Social Demographic table has over 50 fields, containing total number
> (count) of people. No inserts are made to this table, however, the data is
> updated when new data is received. Quite often, we receive data from
> clients. This data contains postcodes, so we join these tables together,
> to give customers social
>
> From a personal perspective, 55 fields is too many for 1 table. I think it
> would be better to break the tables, and then join them using a view. Each
> year, the data is updated.
>
> This is how I'm thinking of going about it.
>
> 1. Create a table that has all the postcodes:
>
>
> CREATE TABLE t_PostCode(
> PostCodeId int IDENTITY PRIMARY KEY
> PostCode char(6) not null)
>
> 2. Create a table that contains a description of the different types of
> homes
>
> CREATE TABLE t_HouseType(
> HouseTypeId int IDENTITY PRIMARY KEY
> Description varchar(20) not null)
>
> A row in this table would look like this:
> 1 Apartment
> 2 Terrace
>
> ...
> 3. Create a table that stores the total number of houses in a particular
> postcode
> CREATE TABLE t_PostCodeHouseType(
> PostCodeHouseType int IDENTITY PRIMARY KEY,
> PostCodeId int FOREIGN KEY REFERENCES t_PostCode(PostCodeID),
> HouseTypeId int FOREIGN KEY REFERENCES t_HouseType(HouseTypeID),
> TotalNumber int )
>
> 4. Create a table that stores the age of the head of the household
>
> CREATE TABLE t_HouseHoldHeadAge(
> HouseHoldHeadAgeId int IDENTITY PRIMARY KEY,
> Description varchar(20)
> )
>
> 5. Create a table that stores the details in point 4 for each postcode
>
> CREATE TABLE t_PostCodeHouseHoldHeadAge(
> PostCodeHouseHoldHeadAgeId int,
> PostCodeId int FOREIGN KEY REFERENCES t_PostCode(PostCodeID),
> HouseHoldHeadAgeId int FOREIGN KEY REFERENCES
> t_HouseHoldHeadAge(HouseHoldHeadAgeId ) )
>
> I would really appreciate any feedback on my approach. I am by no means an
> expert at data modelling, but I want to do this correctly.
>
> Thanks in advance.
>
> Linda

Compiled marketing database?

Is this database still receiving active automated feeds, or is it "dead" and static? Are there any manual entries?

Are you using this to provide overlays to outside parties, or for internal analysis?

In my experience, an active marketing database is usually very much non-normalized, and this is a Good Thing in terms of delivering to your customers. The design you have may be perfect in terms of the business you are in.

In short, I think the more important questions might be, what is the nature of updates to the system (batch or interactive), what are customers used to in terms of queries, and what tools are you using to query? Once these are settled, then you ask if the model is "correct."

-- 
Kenneth Downs