I would set the fields with no payments to NULL. that would indicate to me there was no payment. A zero indicates to me that a payment was made, but, it was zero. Some applications need to know whether the payment was entered in error (zero or any other number for that matter) or if a payment was never made (NULL).

Why would having a record with zero make the database un-normalized?

In general you should drop the rows holding zero because there's sometimes a difference between zero and null. We for example sometimes have a sale of zero value between sites. A zero in the sales file means "There has been a sale but we didn't charge" whereas a null would mean "Nothing at all has happened".

This isn't to say you should be explicitly adding null values. A Left Outer Join between sites and sales will return all sites whether or not they've got any sales and and will automatically give null as the sales value for those sites that have no sales.

Geoff Franklin
www.alvechurchdata.co.uk

What does the payment column data mean? (The 10, 20 etc numbers.) Is it a transaction id, or number of sales that year, or customer id, or year revenue, or year profit???

If its a sum of something I'd say keep 0 value if the entity existed that year. Set to null if the entity was (temporarily) shut down. Remove row if entity didn't exist.
 

Quote:

Why would having a record with zero make the database un-normalized?

I don't know about these things.  I just always thought that if some of your records had data in x number of fields and some in x+2 fields and some only in x-3 fields that you weren't fully normalized yet.

 

Your table is in Strong Third Normal Form.

I don't know precisely what your data is but it looks like the zeros are valid, and better than null. (Null means 'doesn't apply'/'don't know'/'not available'/'could be allsorts'. If you use nulls you can't easily ask Access for those months when turnover was less than 15).

Also remember that normalisation is not mandatory. De-normalisation is just as valid a DBA activity as normalisation.

I'd add, the question to keep or delete these rows is not a normalization question.

I assume this table stores which entity got which payment in which year and you would not store records with no payment. I further assume there is another table with all entities and to get the information which entity got paid what in which year you'd do a left join of the entities with this, filtering for a certain year and thereby get a NULL for each entity with no payment in that year at all.

If you add that record with 0 payment you get a joined value of 0 instead of a non join leaving the field NULL.

Neither is wrong and I assume it's more natural to store no record for no payment in some cases and to explicitly store a record with 0 in other cases. Your reason to assures that no entity was overlooked seems plausible. Eg if that data reflects bonus payments of employees it's plausable to have that extra record there to denote no bonus is assigned, but the employee wasn't overlooked.

What I would never do is make the payment NULLable, you'd rather not at all insert a record in case an employee isn't among those receiving a bonus anyway instead of storing that with 0 or NULL payment. The NULL is created by a join of the entities table with this payments/transactions table.

Bye, Olaf.

"I'd add, the question to keep or delete these rows is not a normalization question."

Normalisation is about avoiding substructure in your data.

Examine each table and see if you can find a set of fields which always determines the value of another field (a determinant). If there isn't one, then you don't have First Normal Form ie you must have a key of some sort for a table to be a relation. If on the other hand you find such a group of fields that isn't also a key (not necessarily the declared key - you can have more than one), then you don't have Third Normal Form which is the one we most commonly refer to.