Data Model - Please Comment on my Approach

From: Linda <lindadok_at_xs4all.nl>
Date: Fri, 23 May 2003 20:02:47 +0200
Message-ID: <3ece6231$0$49114$e4fe514c_at_news.xs4all.nl>

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 Received on Fri May 23 2003 - 20:02:47 CEST