Simple Table Design Basics - Extra Column or Rows ?

[Powermac]

Hi all,



I have a very basic question regarding the design of a simple table (just using Access very pragmatically and no advanced Access user or database expert).

Having different cost categories (accounts) with monthly values for different locations: Should I create an additonal column for the cost values for each account (picture A) or create a seperate row for each value with the account ID in one column and the cost value in another (picture B).

A:


B:


Many thanks ahead!

Best regards

[Bob Fitz]

IMHO table B would be best. Table A looks like a spreadsheet design and that always leads to problems in Access. Long, narrow tables are better than wide shallow tables. The need for repeating field names that are then numbered (Account1,Accouont2 etc) are an indication that the data is not Normalized and that another table is required for the data.

[pbaldy]

Another vote for B if you were wanting other opinions. What are you going to do when they add account 5? With A you have to add a field to the table and modify every query/form/report accordingly. With B, all you do is add a record to the "accounts" table and you're done. Actually the users would probably do that via a form you give them to manage accounts. Adding a new product/account/whatever should not require design changes.

[isladogs]

B for the same reasons as above. Suggest you read about normalisation of data

[Powermac]

Hi all three,

thanks a lot for your support! All very helpful for me! Your comments made it much clearer now.

I read about normalisation of data but for some reason I was not sure in this case.

Thanks!

[Powermac]

I would like to raise one additional question that came into my mind regarding a similar cost table where I have an order ID and several (19) types of costs for each order. When applying the same logic (B) here, I get many many lines. As I have to mention the order ID and the cost element ID in each row again, the database gets much bigger compared to the spreadsheet-like scheme (A).

Should I create the table still as on picture below?



Regarding normalisation: Would a table in spreadsheet-like design A (with a column for each cost element) violate normalisation?

Thanks again!

[pbaldy]

In my view, yes and definitely yes. As Bob mentioned:

Long, narrow tables are better than wide shallow tables.

[CJ_London]

Would a table in spreadsheet-like design A (with a column for each cost element) violate normalisation?

definitely

the database gets much bigger compared to the spreadsheet-like scheme (A)

size should not come into it. What matters is good database design. Applying excel principles (which combine storage with presentation) is not the way databases work. tables store data whilst queries, forms and reports present data. Go down the excel route and you will quickly run into trouble.

Besides, your table B does not require a record for location 1, account 1, period 05/2020. Your picture in post #6 has 6 rows which are not required.

Also, none of your tables have a primary key.

[June7]

Another vote for B. Maybe event split to two tables. Depends how much repetition want to eliminate.

[Powermac]

In my view, yes and definitely yes.

Thanks!

definitely

size should not come into it. What matters is good database design. Applying excel principles (which combine storage with presentation) is not the way databases work. tables store data whilst queries, forms and reports present data. Go down the excel route and you will quickly run into trouble.

Besides, your table B does not require a record for location 1, account 1, period 05/2020. Your picture in post #6 has 6 rows which are not required.

Also, none of your tables have a primary key.

Thanks! Very helpful comments.
Yes and actually lots of the orders don't have values for all of the elements, so all these could be eliminated. Understood.
Is a primary key always required or recommended if each record is uniquely identified by the combination of foreign keys? Each combination cannot exist more than once.

Another vote for B. Maybe event split to two tables. Depends how much repetition want to eliminate.

Thanks! Do you mean the first example or the second? In the second, I cannot see how it could make sense to split into two tables. Rather in the first.

[orange]

Powermac,

I recommend you work through 1 or 2 of the tutorials from RogersAccessLibrary in which he describes a business and leads you through a procedure to identify tables and relationships. The tutorials come with a problem statement/narrative, process steps and a solution. Each tutorial will take about 45 to 60 minutes to complete. But you will learn about and experience Normalization and what you learn can be used with any database. You have to work through the tutorial(s) to gain the experience.

Tutorials from RogersAccessLibrary

ZYX Laboratories
Class info system
Catering Business
Widgets

Good luck.

[Powermac]

Thanks orange for the tip with the tutorials. I'm going to follow your recommendation and work through it!

[CJ_London]

Is a primary key always required or recommended if each record is uniquely identified by the combination of foreign keys? Each combination cannot exist more than once.

you can use a composite index as a primary key but it becomes unwieldy. Personally, I wouldn't.

If the requirement is that each combination is unique, you can still have a composite index set to no duplicates

[June7]

Two tables could be like:

tblEvents (or whatever in place of Event)
EventID_PK
LocationID
Period
EnterBy
etc

tblEventDetails
EventID_FK
AccountID_FK
Amount

Use form/subform arrangement for data entry/edit.

[ssanfu]

I am in agreement with Ajax.

...Is a primary key always required or recommended if each record is uniquely identified by the combination of foreign keys? Each combination cannot exist more than once.

I tried using a composite primary key in my early days of Access development... a real PITA.

Lets say you have a customer table with a composite PK of FirstName, MI, LastName and Address1. You add two tables that should be linked to the customers table. To create the link,you have to add the composite PK fields from customers to each of the tables - that is 3 extra fields per table to create the foreign keys. Any operation where you need to use the PK requires those 4 fields.

I have an autonumber in every table as the PK field. I know (have been told ) that it is overkill, but I have a unique identifier in every table, even if I don't use it. If I end up needing to use it, it is there.
IMO, the design is cleaner/easier and queries are easier to create. If I need to have a unique combination of 2 or more field that cannot exist more than once, I use a compound index and set it to be Unique.

Again, this is just my style.


Also see:
Microsoft Access Tables: Primary Key Tips and Techniques