×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Should I have empty records or just get rid of them?

Should I have empty records or just get rid of them?

Should I have empty records or just get rid of them?

(OP)
I have payments to entities over years.  Not each entity gets a payment in each year.

For spreadsheet purposes I like to see an entry and a value even if no payment is made.  This reassures me that the entity wasn't overlooked.

Simplified example:

CODE

entity year payment
1      1    10
2      1    20
3      1    0
4      1    10
1      2    20
2      2    0
3      2    10
4      2    20
1      3    0
2      3    10
3      3    15
4      3    10

For database purposes, however, as I am trying to think good thoughts about normalization, I wonder if I ought to drop the rows with zero for a payment.  We're not talking huge numbers of records or a major commercial application, I'm more just interested in what is best practice.

If one replaced the zero payments with blanks (functionally the same thing- no payment) I would view the database as not rectangular and not normalized and drop those rows.

But like I say, I sort of like the zeros.  If the records were just missing instead, I would feel like I didn't know if the payment amounts were really zero or if the data were actually really missing.

 

RE: Should I have empty records or just get rid of them?

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?

rockband

RE: Should I have empty records or just get rid of them?

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

RE: Should I have empty records or just get rid of them?

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.
 

RE: Should I have empty records or just get rid of them?

(OP)

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.

 

RE: Should I have empty records or just get rid of them?

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.

RE: Should I have empty records or just get rid of them?

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.

RE: Should I have empty records or just get rid of them?

"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.  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close