Proper table design

[doma23]

At the moment I have something like this:

tblRevenues
Comp____Country ____Period____P roduct____Reven ues
ABC_____Germany ____ 1H ______ Pr_1 ____ 125
ABC_____Germany ____ 1H ______ Pr_2 ____ 80
ABC_____France_ ____ 1H ______ Pr_1 ____ 9
ABC_____France_ ____ 1H ______ Pr_2 ____ 7

tblRisk
Comp____Country ____Period____R isk
ABC_____Germany ____ 1H ______ 40
ABC_____France_ ____ 1H ______ 10

What are the pros/cons if I design the tables following way:

tblRevAndRisk
Comp____Country ____Period____P roduct1____Prod uct2____Risk
ABC_____Germany ____ 1H ______ 125________ 80 ____ 40
ABC_____France_ ____ 1H ______ 9 ________ 7 ____ 10

Obviously with this design I would have much lower number of rows, and Risk and Revenues are both in just one table. But how much of an advantage is that?
Something tells me that I would have problems later with queries and reports, but I can't see at the moment what kind of problems I might have.

I'm actually transferring data from Excel that are in this format. I'm manually converting them to the first structure. I guess there should be a way to program it in VBA so that it would be done automatically, but it might take me more time to do that than to do it manually.

Thanks!

[doma23]

Anyone?
Damn, I thought this was simple.

Now I'm finding another problem, in the report I would need the data to look like on the second version of possible table structure.
Like this:

Comp____Country ____Period____P roduct1____Prod uct2_ ___Risk
ABC_____Germany ____ 1H ______ 125________ 80 ____ 40
ABC_____France_ ____ 1H ______ 9 ________ 7 ____ 10

How hard is it to transform the data from the first format to the one it should be on report?
I thought it was easy, but having troubles making it done.
I put "Country" in Page Header, and "Company" and "Revenues" in Detail section. But this just put one country per page, and replicates unnecessarily the Company Name and Revenue throughout the each page.

EDIT: OK. I solved the report layout problem with Crosstab report. That was actually quite simple.

[Seth Schrock]

According to the Normalization Theory http://http://www.databasedesign-res...alization.html you should't have two fields in the same table with like data. For example, you wouldn't have field for product 1 and product 2 as you have in your combined table design. There are a few problems with this type of design that I can think of. First, if you would ever need to add a third product, then the amount of redesign would be huge. Second, it makes it much more complicated to do a query for all the products for a company and to do anything with the data. I would strongly suggest reading the normalization theory (linked above) as it will really benefit your database design.

Without complete knowledge of what you are trying to do, it is hard to make an accurate suggestion of how you should design your tables. If you want further help, you will have to give a better description.

[doma23]

Got it. Thanks!
Website is bad though. :)

[Seth Schrock]

I'm not sure why the website doesn't work, but you can just do a Google search for Database Normalization Theory and you will find plenty of things to look at. I don't normally trust wikipedia too much, but the article about the normalization theory is good.

[doma23]

Yeah, I've read quite some today, now things are bit clearer.
Anyway, I have another question.

I have a lot of records where revenue is zero, I would say 80% of them. Later I will need to do a lot of period comparisons. For example, 1H12 vs 1H11, and so on.

I'm wondering would it be good idea to delete all the records where revenue is zero, in order to reduce the number of the records and speed up the database?
Would it be possible later to do comparisons even if there is no record for one of the periods.

I.e. If I want to compare 1H12 vs 1H11, but some records are missing for 1H11 for certain companies, can I use some formula in the query which would find out whether record exist, and if not, it would use zero instead..?

Hope I made myself clear.

[Seth Schrock]

I've never done comparisons with some of the records having the compared field blank so I don't know how Access will behave. However, I think that I have heard that when doing comparison, Access doesn't like null values. One way to fix this so that you don't have a null value would be to use an IIF statement in the sql code like the following.

SELECT IIF(IsNull(Period),0,[Period]) AS NoNullPeriod
FROM tblRevenues

You can then do comparison on the data in the query instead of the table and you wouldn't have any records with a null value in the Period field.

By the way, you shouldn't ask two questions in the same thread. It makes it confusing when you choose best answer because you don't know which question was answered.

[doma23]

All right, I'm not talking about null fields, but about not having the record at all. But, I'm gonna open new thread.