Is it better to have multiple tables?

[TaiChi56]

I am building a gun inventory database for a friend. He needs to have information such as , gun price, sale price, manuf, caliber, serial number, model, so forth.

My question is; would it be better to have severa different tables? I. E.

Table 1
Manufacturers
Table 2
Models
Table 3
SalePrice, PurchasePrice, SerialNumber, DatePurchased, BuyersAddress, so forth.

What would be best. I would not be best to have it all on one master table, would it?

 

[fliflo]

Yes is better. You will have less redundant information in your database.

 

[ScottGem]

The idea of a relational database is to reduce/eliminate redundant data. The fewer tables you have the greater the potential for redundancy.

 

[fliflo]

In this tutorial you can find more reasons why it is better.

_http://fisher.osu.edu/~muhanna_1/837/MSAccess/tutorials/relation.pdf

 

[ejstefl]

The answer to the question really depends on two factors: how big the collection is, and what the database will be used for.

If he has 20 guns, the normalization process is a lot of work to go through for not a lot of gain. If he has 2,000, then it is probably worth it.

Similarly, if you just want a simple place to store this info, its probably OK to not normalize it. But if you will be doing any type of reporting, etc. normalization will make your life easier.

 

[Pat Hartman]

If he has 20 guns, the normalization process is a lot of work to go through for not a lot of gain. If he has 2,000, then it is probably worth it.

Similarly, if you just want a simple place to store this info, its probably OK to not normalize it. But if you will be doing any type of reporting, etc. normalization will make your life easier.

I have to disagree. If you are unwilling to make the effort to use the tool correctly, you probably shouldn't use it. Spreadsheets work fine for simple lists. There is no reason to use Access to maintain a simple list. Excell has functions that work vertically and horizontally. I find that when people just transfer their spreadsheet into Access and call it a table, they are very disappointed in Access because it is more difficult to work with in Access than it was in Excel. However, if they had taken the time to properly normalize the data, they would have found Access to be both easier and more efficient.

 

[ScottGem]

You beat me to it Pat. I'm a great believer in using the right tool for the job. And using the right tool correctly. But even more than that. Maybe this time it is a small database but the time spent in doing it right may pay dividends in showing how other tasks can benefit from a database.

 

[ejstefl]

Pat and Scott,

I have to agree. I was implying, but didn't come out and say, that if its not worth normalizing the data, its not worth using Access to begin with.