473,320 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Proper table design

doma23
107 100+
At the moment I have something like this:

tblRevenues
Comp____Country____Period____Product____Revenues
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____Risk
ABC_____Germany____ 1H ______ 40
ABC_____France_____ 1H ______ 10

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

tblRevAndRisk
Comp____Country____Period____Product1____Product2_ ___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!
Nov 7 '11 #1

✓ answered by 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.

7 1614
doma23
107 100+
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____Product1____Product2_ ___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.
Nov 7 '11 #2
Seth Schrock
2,965 Expert 2GB
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.
Nov 8 '11 #3
doma23
107 100+
Got it. Thanks!
Website is bad though. :)
Nov 8 '11 #4
Seth Schrock
2,965 Expert 2GB
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.
Nov 8 '11 #5
doma23
107 100+
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.
Nov 8 '11 #6
Seth Schrock
2,965 Expert 2GB
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT IIF(IsNull(Period),0,[Period]) AS NoNullPeriod
  2. 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.
Nov 9 '11 #7
doma23
107 100+
All right, I'm not talking about null fields, but about not having the record at all. But, I'm gonna open new thread.
Nov 9 '11 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Dan Williams | last post by:
I'm trying to do a simple alteration to the table design of one of our SQL 2k tables, simply changing an identity row so that its not 'not for replication', and its taking absolutely ages to do so,...
1
by: mksql | last post by:
As an example, I am building an authentication mechanisim that will use data in the 3 left tables to determine rights to objects in a destination table, diagrammed below. In this structure,...
2
by: deko | last post by:
This may be an easy question, but for some reason the multiple table design idea is throwing me. I'm trying to avoid using one large, wide table - so I've got multiple tables that hold different...
8
by: Stewart Allen | last post by:
Hi Just asking for ideas on table design. The design I have is as follows: *tblBuildData* BuildID (PK) AutoNumber ManufactureDate SerialNumber
5
by: BerkshireGuy | last post by:
Hello everyone, I want to create an employee license plate database and need help with the best table design. I was thinking three tables: 1) tblEmployees EmployeeID EmployeeName
6
by: MLH | last post by:
If I open an A97 table, resort its key-field to descending order and attempt to close the table, A97 asks me if I wish to save the table DESIGN? Now really, I don't think the table design is being...
4
by: lorirobn | last post by:
Hello, I'd be curious to hear other thoughts on my database and table design. My database is for 'space use' in a lodging facility - will hold all spaces, like rooms (lodging rooms, dining...
3
by: shahram.shirazi | last post by:
Hi guys, I was wondering if someone could help me a bit here. Im trying to desing an electronic register system for a school. In terms of the table design, I obviously need a Student Details...
1
by: keliie | last post by:
I have a relatively simple (I assume) issue which I am at a complete loss to address. My issues is: I want to populate fields in my tables with summary data from the same table. Let me explain: ...
0
by: jsimone | last post by:
This question is about DB2 table design and performance. We are using DB2 UDB Enterprise 8.2 on Linux. We have 6 tables in a parent-child (one-to-many) relationship with each other. Each...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.