Normalisation of Dates (1 Viewer)

[vegetables2001]

Sorry, newbie here, I apologise if this in the wrong forum, however, here goes: In normalisation no piece of data should be duplicated within a database - however is it really a worthwhile practice to place a just a date ( plus key of course) in a table on its own?

 

[Kryst51]

We need more info to be able to help you. You have given next to no contextual information for anyone to be able to form an answer to you. Please provide context.

Edit: btw, Welcome to AWF!

 

[pbaldy]

I'm no normalization expert, but I never do.

 

[Kryst51]

I'm no normalization expert, but I never do.

Actually, I second this..... But I'm still curious as to why the OP asks, I wonder what his/her question points out about his/her understanding/ or lack of understanding about normalization.

 

[vegetables2001]

OK, I'll try to put it context. Building a database to record when lorries visit a particular shop.

ie.
unit shop date
DK55 BHP Leeds 3/9/2009
YR59 YSS Sheffield 6/6/2010
YR59 YSU Wath 25/11/2009

This is just a little bit of the whole database, but should illustrate what I want to do.

I understand I'll have to take out unit and shop and put them in another yable but is it worth putting date in a new table.

 

[pbaldy]

I'm pretty much self-taught and never took a class on normalization, but it sounds like perhaps strict normalization would have a date table with a key, so if today was our first day the ID might be 1. In a transaction table instead of storing 7/1/10 we'd store 1. I don't know if this is actually a formal theory of normalization, just speculating.

I can say that I've seen postings by people I would consider experts on normalization, and they did not separate the date out either.

 

[vbaInet]

That's your only way of knowing when a visit was made at a shop by that lorry. So from the Unit you should be able to know the location of the shop and the shop ID too.

 

[Brianwarnock]

I am no expert on normalisation either, but I always thought that the purpose of normalisation was to allow the DB design to be flexible enough to cope with Business changes, now your shops may / will change but the dates?

In other words you don't want to have to change the DB design for normal business changes, you just want to add new data.

Brian

 

[vegetables2001]

I thought normalisation was there to:

1. Allow easier updating of record.
2. Reduce database size.

I had pretty much come to the same conclusion myself as you guys above but I was worried I might have missed something obvious.

Thanks for the advice guys.

 

[vbaInet]

That's it Brian. Allow for flexibility without needing to change the design or add more fields. However, there are certain (but rare) changes in business needs that would require a change in design (hopefully not drastic)

With Paul's suggestion, if you aren't going to track the dates a visit was made then a 1 would suffice.

If you want to be able to see a crosstab of visits made within a month (for example) then you must save the date.

So that example you provided shouldn't actually be a table, but should be a query that is pulling from several tables.

To store a visit the fields you need are Unit, LorryID and Date. You can have composite keys comprising of all three fields.

 

[dkinley]

Here is my .02. Normalize only when it makes sense. My chief reason is to serve data-integrity purposes, for db flexibility and hopefully to create an optimal ...

For database size, it is my understanding that Access a long integer type is 4 bytes and a date field size is 8 bytes. So strict interpretation would probably lean to another level or normalization.

I don't think 4 bytes is too much to quibble about and as lagbolt said in a previous post, "These days I think data storage efficiency should play second fiddle to data storage simplicity and ease of use."

As been stated time and time again ... Why would you want go through the headache of doing a lookup with an add-to-field and the query building nightmares if storage is pennies on the MB?

-dK

 

[gemma-the-husky]

op

its not that there should be no duplication of dates. obviously more than 1 event could happen on july 1st.

what is important is that if the date is dependent on something else, then storing the date is redundant.

so you could have something with a start date, end date and duration

now you dont need to store all 3 of these, since you can work out the third from the other two. and indeed if the duration is always the same, then you only need to store the start date.

that's what normalisation is getting at. Split your data into relevant entities, and store each individual datum in the appropriate entity. And if you find that you have repeating data, (or problems with data dependencies) then redesign to remove it.


----------------
so in your example - is the post code, the post code for the shop - if so THATS what you need to take out

then you get

shop table (shopid, post code, store name, address, phone number, manager etc etc)
(the shop id is an artificial entity to make things easier later)

delivery table (date, shopid)

------------
now - consider further - do you actually have delivery routes, taking in a number of shops

in which case you might want

route table (routeid, date, routenumber)
(the route id is an artificial entity to make things easier later)

route details (routeid, shopid, droporder, deliveryresult)
(ie all the stores delivered to on the selected route)

--------------
so now you have a route consisiting of a number of shops visited - and the route table stores the date of the visit - and now you can add things like time spent, distance covered, vehicle used, vehicle driver etc - all to the route table - so for each route, you store the data only once

now if the drop order is significant (as it normally is) then you can store the drop order in the route details table. and if you want to record details of the delivery result (success, failure code) this belongs in the route details table.

so heres an example - you can either store the total distance for the whole route, in the route table - or you can store the distance for each drop in the route detail table - but you probably shouldnt store both, as the total can be derived by adding the details - and is therefore redundant. even so, if its data you need regularly, some designers may take a considered decision to store the totals regardless.


hope this helps

 

[The_Doc_Man]

From what you've told us, you actually aren't ready to normalize yet. Here is the rule set that I try to remember.

1. Access won't tell you anything you didn't tell it first. (I.e. if you need that date later, store it now. Or forget about it.)

2. If you can't do it on paper, you can't do it in Access. (I.e. you must already have a design for how to get from point A to point B before you try to implement query or code to do that for you.)

Once you know what you need for your problem; once you have identified the things you need to store for later use; THEN AND ONLY THEN is it right to actually bother to normalize.

Now, there are other issues. Normalization to a separate table is probably NOT right if the only other thing in that separate table is a key. Particularly it is wrong if the key is not natural but synthetic. (Search this forum for the topic "Meaningful vs. Meaningless" and variant word order/spelling.)

In the discussion I see, I would never ever put the dates in a separate table with only a key field unless that key were highly meaningful. Otherwise, you take up LESS space (one of the goals of normalization) by just storing the date in with related data.

Usually, dates are not unique across a large event-recording database. They are therefore not very good candidates for being a primary key (PK) field themselves. They are more like dependent data for something else that might be a valid PK.

In your business model, you talk about visits. It isn't the dates that count so much as the fact of a visit that happened to be on that date. Focus on events (e.g. a visit), not attributes of those events (a date or a truck number). The rest will start to fall into place later.

Normalization might occur if like this: A visit could get an arbitrary sequence number, a date, and a truck number. To validate the truck, you would normalize by splitting that off to another table, but then enforce relational integrity so that you could set up a drop-down on your data entry form to pick the truck. The date should not be separate because here it would only be dependent information, probably not a candidate as a PK.

This is one example of the thought processes that should occur when deciding how & when to normalize.