How many tables?

[Jim Orson]

Hi,

I am setting up a home inventory database with the following fields:
location, category, description, brand, cost, model, purchase date, manual
(y/n), receipt (y/n) and maybe a couple of others.

From what I read, it is better to have 3 related tables (location, category,
all the others) than 1 single table. Why? What are the advantages of 3
related tables over 1 flat table for just a few hundred records at the most?
It would seem easier to set up with just 1 table. BTW, I envision just 2 or
3 reports (1 with all data for insurance purposes, 1 with description and
location for a file index, and ???).

Any comments would be appreciated.

Jim Orson...

 

[Roger Carlson]

In your case, maybe it's not important to normalize your data. If it's just
for you, you know the data will be limited, and you are confident you can
resolve any data anomolies, then by all means, use a flat file. Many
reporting systems or decision support systems use a form of flattened tables
called a Dimensional Model.

However, the reasons for normalizing your data still exist. You will have a
lot of redundant data. Redundant data means that data that SHOULD be the
same can become different. For instance; say a Location is called "Living
Room". If it gets input (or changed somehow) to "LivingRoom", you now have
a different location and it will report differently. You will also have to
input this redundant data over and over, and of course it will cause your
database to be larger.

But as I said, if you can resolve these problems manually and the database
remains managably small, a flat file can work just fine. Just don't try to
scale it up to manage a store inventory!

 

[Jim Orson]

Thanks Roger, your comments are valuable.

In your case, maybe it's not important to normalize your data. If it's just
for you, you know the data will be limited, and you are confident you can
resolve any data anomolies, then by all means, use a flat file. Many
reporting systems or decision support systems use a form of flattened tables
called a Dimensional Model.

However, the reasons for normalizing your data still exist. You will have a
lot of redundant data. Redundant data means that data that SHOULD be the
same can become different. For instance; say a Location is called "Living
Room". If it gets input (or changed somehow) to "LivingRoom", you now have
a different location and it will report differently. You will also have to
input this redundant data over and over, and of course it will cause your
database to be larger.

But as I said, if you can resolve these problems manually and the database
remains managably small, a flat file can work just fine. Just don't try to
scale it up to manage a store inventory!

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

2