Re: Data Model - Please Comment on my Approach

"Linda" <lindadok_at_xs4all.nl> wrote in message news: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.
>

55 colums in a table is a red flag, but there is no fixed rule about how many columns a table should have. A table should have one column for each atomic attribute of the entity represented by the table. Some entities have many seperate atomic attributes.

A lot depends on how the data is entered and updated. If it comes in only once a year, and comes already summarized to the postal code level, then I would be very hesitant to break it into multiple tables. You'd just be making more work for yourself.

Plus, I would think that the logical way to break this down is to the household level. And if you don't have household detail, then you're probably better off leaving it like it is.

David Received on Fri May 23 2003 - 20:43:29 CEST