Put attributes into one or two tables? (1 Viewer)

Noruen

Member
Local time
Today, 16:02
Joined
Jul 7, 2020
Messages
46
Hi,

I'm thinking about design of my database of expenses. The issue I have is when I put expense related to car, i want to add also info about (other than Autonumber ID and related expense ID):
  • CAR ID
  • Odometer value
  • and in case of Fuel also fueled liters.
And that is the problem. Do I have to create two tables, one for fuel and one for "other" costs? If only one - is it ok that almostly half of field "Fueled liters" will be empty in the table? It is probalby more problem of my OCD, but I want to do it right :)

Current status of my expenses is:
Other costs: 387 items
Fuel: 377 items

Many thanks!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:02
Joined
May 7, 2009
Messages
19,230
you need at least 3 table.
2 master files, tblCar, tblExpenses

tblCar(table)
CarID (autonumber)
CarName (text)
(RegNo) (text)
Other info

tblExpenses(table)
ExpenseCode/ExpenseID (autonumber)
Expense (text)

tblCarExpense(table, the transaction for each car)
ID (autonumber)
Date (date/time)
CarID (FK to tblCar)
ExpenseID (FK to tblExpenses)
Odometer (long)
Amount (double)
 

Noruen

Member
Local time
Today, 16:02
Joined
Jul 7, 2020
Messages
46
Sorry, mi mistake - I already have those two tables (Expenses and Cars). My issue is if it is ok to have one table for exoense attributes where FUEL will be empty for half of items or I have to split this “tblCarExpense” into two - one for FUEL and one for OTHER CAR EXOENSES where field “FUEL AMOUNT” will be missing.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:02
Joined
May 7, 2009
Messages
19,230
you do not need to split the table.
create 2 records, one for fuel (full) and another record for half-full.
make sure to have autonumbers to your tables.
they come in handy when you need them.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:02
Joined
Jan 20, 2009
Messages
12,851
These kinds of decisions are the real art of database design. Every choice means a compromise.

Try to think in terms of simplicity versus the ability to extend the database in future without editing any table structure code or queries. New abilities are added to an advanced application by adding records to tables, not modifying their design. This is why you have the table of ExpenseIds rather than a column for each type of expense in the main table.

Taking that concept further we ask are there other attributes of each Expense record that I want to record? In the case of the fuel you want the amount of fuel which isn't used on other records. In the future you might want to record some other aspect of the expense that you have not thought of now. No worries if you don't think of it up front. Just add it in a table and presto.

While attributes that are on every record might as well be in the main record, any number of additional attributes can be added to an expense record using a related table in what is known as an Entity-Attribute-Value structure. In your case the related table would have fields for the Expense record (the Entity), the AttributeID and the Value. For a FuelExpense an AttributeId may represent FuelAmount, FuelCost, Odometer or anything you want to record with fuel. Other expenses have attributes for them. Meal expenses can have an Attribute as HadForBreakfast if you want. A travel expense might have StartOdo and EndOdo attributes. Whatever the user wants to add.

The downside of EAV is the complexities of the deign using subforms and dealing with datatypes and units which also often need to be in the Attribute definitions. Is the fuel in Litres or Gallons? Odometer in Kilometres or Miles. You can define this with a table of Units for the measurement. Yet another table defines the units that can be applied to an Attribute. You don't want Kilometres available on FuelAmount. You can design the main records to simultaneously support any applicable unit for the same ExpenseType if you like.

There are performance limitations with calculations too if you record every Value as text so it can be better to separate out the numeric data from the text data.

Clearly it is also going to take a lot longer to set up the EAV model. Once done it can be incredibly extensible but will always involve some compromise.

Designing any database depends on priorities and the value of extensibility versus its development cost. If you are likely to need to be adding new attributes then make sure it involves adding records, not changing the design. Designing in the capability at the start will be better value for money over the life of the application, which very often far exceeds the initial expectation, especially if a database application works well.

Of course the customer must be willing to make that investment or the choices become academic.
 

Noruen

Member
Local time
Today, 16:02
Joined
Jul 7, 2020
Messages
46
Thank you! I think I understand now. So one table would be better for now.

Great thoughts Galaxiom, I appreciate it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:02
Joined
Feb 19, 2002
Messages
43,257
Sorry, mi mistake - I already have those two tables (Expenses and Cars).
Arne showed you THREE tables. The Expense table just defines the type of expense. This allows you to use a combo to select the expense type and keeps typos out of the expense field. The third table is the junction table between car and expense and could be named tblCarExpense. This is the table where you would add a record for each expense along with the amount and cost and odometer reading.

tblCarExpense
CarExpenseID (autonumber)
ExpDate (date/time)
CarID (FK to tblCar)
ExpenseID (FK to tblExpenses)
Odometer (long)
Amount (currency*)
Cost(currency)

Currency is a data type as well as a format. As a data type it is a scaled integer and supports a maximum of four decimal digits. Use it rather than single or double whenever you don't need more than four decimal digits. You can format it however you want. This data type avoids the dreaded floating point errors where 1+1 does not = 2.
 

Users who are viewing this thread

Top Bottom