Database design storing Timetrack

[JonnyPoet]

Hi friends !

I'm just starting to redesign our database to get rid of old behaviour, which always was disturbing but never handled.
The problem is known I think.
For explaining I'll take a practical example.
Think to a customer stored in database with name, firstname adressID , phone and all that. AdressID stores the adress where he lives in another table.

The customer changes for example his adress so he simple gots a new adressID no problem.

Some times later he is requesting a bill two years ago. You go to reprint it, but it shows the new adress instead of the old one.
The sam when a woman marries, gets divorced taking back her maiden name and all that. Databas always shows latest data. There is no timetrack . So whats the best way to design it in a new way containing a timetrack. ? How this is done in a preofessional way, and how are the trackdata stored. ( separated table - timestamp - versionnumbers ? )
Any good book about a design performing this features.

Thx in before

Jonny:wave:

[davide++]

Hi all.

Seems that you're using one-to-one relationships to store data about details, so you have always the last version of details; for example, the last customer's address.
You should use one-to-many relationship. So you can store many addresses for a customer, and track the history of changes. Also you can add some specific fields, for example the creation date of address.

[JonnyPoet]

Hi all.

Seems that you're using one-to-one relationships to store data about details, so you have always the last version of details; for example, the last customer's address.
You should use one-to-many relationship. So you can store many addresses for a customer, and track the history of changes. Also you can add some specific fields, for example the creation date of address.This mean´s I need to have an additional table which stores DustomersID, adressID,timestamp ( created ) Ok But how to do with womans maidenname this would need a conceipt of
customersTab: custID , timestamp ( ) , timestamp (modified) nothing else
and then a custNameTable containing
nameID ( unique primary) custID (indexed foreignkey) name, firstname, birthday, titleID ( titles may change during life ) , timestamp created,
an AdressTable which contains

adressID(uniue,primary), custID (foreignkey to customerTab), adressDataID ( The ID off the real adress data )

This table then connects two tables The AsdressDataTable and the customers table.

and my AdressDataTable I just have which contains
adressdataID ( unique, primary ) street, doornumber, city, postalcode

Also a PhoneTab which contains
phoneID (unique primary ) , custID, phoneNumber
an emailTab similar to that all the timestamps for created and modified, or maybe additional a verion number ? Should we have the latest Id always existing in the Customers table like
custID( Primary ) , nameID(actual version), adressID(actual version),phoneID (actual version ) or leads this to other troubles.

Is this the most actual way to get it done ? Is there anywhere a small design example.

[davide++]

Well

I was thinking at the simplest solution.

Customers
custID
Name
Surname
etc.

Addresses
custID
addressID
date
city
street
doornumber
etc.

I moved last year, so


Customers
custID Name Surname
12634 Davide ....

Addresses
custID addressID timestamp city street doornumber
12634 1 Trecate Via Rosselli 26
12634 2 26/03/2007 Trecate Via Trento 18/A


If you want to extract my present address you have to select from Addresses table the MAX(addressID)
where custID = 12634. Or you can add a boolean field that contains true for the present address.
The version number is the addressId.
About names... is it necessary to keep these changes?
The basic information are Name and Surname, and they don't change; you can add the husband's surname for the woman, and manages the changes using updates.

I hope this will help you.

[JonnyPoet]

Well

I was thinking at the simplest solution.

Customers
custID
Name
Surname
etc.

Addresses
custID
addressID
date
city
street
doornumber
etc.

I moved last year, so


Customers
custID Name Surname
12634 Davide ....

Addresses
custID addressID timestamp city street doornumber
12634 1 Trecate Via Rosselli 26
12634 2 26/03/2007 Trecate Via Trento 18/A


If you want to extract my present address you have to select from Addresses table the MAX(addressID)
where custID = 12634. Or you can add a boolean field that contains true for the present address.
Thats quite OK. But then when you have this with lots of customers and you have to reprint a bill and he has maybe three times changed his adress.
Last time he changed was maybe 1.1.2004 before that he has changed on 1.1.2000 and before that he moved in 1995 The needed bill is of date 2002 so you want to get his adress in that time. you easily can search for all his adresses, sure only listing all of them but whats the query to exactly get that one which was the actual in 2002 (Thats that one created in 1.1.2000. If I asc for modDate < 1.1.2002 then I'll get two answers (1995 and 2000) Do I need to have tow queries then fist looking for all of them with modDate less then 2002 and in the result again asking for Max(modDate)? (I'm not very good in SQL Queries )
The version number is the addressId.
About names... is it necessary to keep these changes?
The basic information are Name and Surname, and they don't change; you can add the husband's surname for the woman, and manages the changes using updates.
As a private service we normally cannot ask for the maiden name as long as people are married. We only find it out, when people after divorce comes to shop and wants their old and their new files in their service history. This is a very complex cycle as we have to differ which devices are gone to her and which to him, so files needs to be splitted. In the moment we do this but the history is gone. We have no idea, since when a file is splitted and that it was splitted after this was done.

The new archive I'm just starting to design should be able to track a lot more, like e,g we get orders by firms who are housemanagers. There are different people working and sometimes we need to know years later about which person has given a destinct order. In the moment we do this in searching for the documents. But often we cannor really follow this because there are so lots of paper :eek: This people doesn't work all the time in the same firm They change job and are suddenly working in other firms. You meet this person and you thing.: I know him /her, but where from do I know him ? Where to look if you dont rmember.

All this timetracks should be able to be watched and tracked because good data is much money in the end. Sounds strange, but knowing your friends and your enemies by data is a great help in business. So my request is, what methods are used in the moment in construction of complex databases. Therefore also the question for a good book regarding that theme ?

For example: Who has recommanded you is an often aked question and people like to answer it. But having this information stored will show you the private network behind your business success. If one of the people comes asking for some free service, because he's 'such a good customer' in the moment we can have a look to his files and what he personally has bought and then deciding to give the service or to deny.
When his files was splitted as he was divorcedand all of his electronic has gone to his wife.... We think he's a poor customer telling stories only. In reality he's a really good one but his files are splitted and we cannot see that he has bought for 30.000 US three years ago. :blush:

So if I dont give him the service for free I maybe lost a good friend who has brought you lots of money ? Or maybe he only tells you 'a story' about how much people he has recmmanded, but you have no possibility to look at the track of his commlines or his successful recommandations. A lot of wrong business decisions would not be done if you are able to look at this.

You maybe think this sounds like a private spy system ? No. But as more data you have as better you can decide in business as better you can service him. For quality service you need to have tracks about the history of electronic devices, Which 'illness' they have had already. ;)
This gives muc better decicios for a customer when he has to change to a new device. All that. Lot of stuff. My actual system isn't that bad, but it works using VB and VBA and less of queries ( I was working more with keys and looping through recordsets. But this is too slow.
The new system will work in C# and based on sql server 2005. But before starting this, I need to do research about the design. Thx or your help in between

[Alsvha]

Thats quite OK. But then when you have this with lots of customers and you have to reprint a bill and he has maybe three times changed his adress.
Last time he changed was maybe 1.1.2004 before that he has changed on 1.1.2000 and before that he moved in 1995 The needed bill is of date 2002 so you want to get his adress in that time. you easily can search for all his adresses, sure only listing all of them but whats the query to exactly get that one which was the actual in 2002 (Thats that one created in 1.1.2000. If I asc for modDate < 1.1.2002 then I'll get two answers (1995 and 2000) Do I need to have tow queries then fist looking for all of them with modDate less then 2002 and in the result again asking for Max(modDate)? (I'm not very good in SQL Queries )
<snip>
Using Davide++ design, which is one (good) way of doing it, you will of course have to expand a number of your queries because when ever you want to store additional dimensions of information, you'll have to expand your model and thus your queries.
For extracting the data regarding address, given his design, you could simply make a query which could accept a cut-off date and then select the date which was active at that given time by comparing the timestamp. It is what is often done with for example price information for a product which in many systems comes with a date to indicates its "active" period so to speak.


<snip>
As a private service we normally cannot ask for the maiden name as long as people are married. We only find it out, when people after divorce comes to shop and wants their old and their new files in their service history. This is a very complex cycle as we have to differ which devices are gone to her and which to him, so files needs to be splitted. In the moment we do this but the history is gone. We have no idea, since when a file is splitted and that it was splitted after this was done. <snip>
You do not need a maiden name for this, you can simple change the surname to another value.
The name should not be a key of any kind, so it should be a value you can easily change.
There is no reason to keep the historical changes, lest you have some actual good reason to do so.

Basically - I can think of only a few situations where databases should keep historical data of name changes, and none of them have anything to do with order systems.


<snip>
The new archive I'm just starting to design should be able to track a lot more, like e,g we get orders by firms who are housemanagers. There are different people working and sometimes we need to know years later about which person has given a destinct order. In the moment we do this in searching for the documents. But often we cannor really follow this because there are so lots of paper :eek: This people doesn't work all the time in the same firm They change job and are suddenly working in other firms. You meet this person and you thing.: I know him /her, but where from do I know him ? Where to look if you dont rmember.<snip>

Simply have a contact person reference on your order.

<snip>
All this timetracks should be able to be watched and tracked because good data is much money in the end. Sounds strange, but knowing your friends and your enemies by data is a great help in business. So my request is, what methods are used in the moment in construction of complex databases. Therefore also the question for a good book regarding that theme ? <snip>
Well, you can also store too much information. If information is only used occasionally, then start question whether it is needed in the system.
A too bloated database can quickly become ineffective and then rather useless.
Also be sure to know the laws on data mining in your country - what you may store, how long and all that. There are different laws for different countries.

<snip>
For example: Who has recommanded you is an often aked question and people like to answer it. But having this information stored will show you the private network behind your business success. If one of the people comes asking for some free service, because he's 'such a good customer' in the moment we can have a look to his files and what he personally has bought and then deciding to give the service or to deny.
When his files was splitted as he was divorcedand all of his electronic has gone to his wife.... We think he's a poor customer telling stories only. In reality he's a really good one but his files are splitted and we cannot see that he has bought for 30.000 US three years ago. :blush:

So if I dont give him the service for free I maybe lost a good friend who has brought you lots of money ? Or maybe he only tells you 'a story' about how much people he has recmmanded, but you have no possibility to look at the track of his commlines or his successful recommandations. A lot of wrong business decisions would not be done if you are able to look at this.
<snip>
Again - name changes or marital status should never have been a factor in this. I think there is something wrong with your design here.
Names should not be keys, so name changes should just be the changing of a field, so when the customer changes name - update his account. Similar with marital status - I can't even start to phantom how getting divorced could have any influence on historical data regarding orders.
I also seriously doubt you actually need to have any information regarding marital status in your system?


<snip>
You maybe think this sounds like a private spy system ? No. But as more data you have as better you can decide in business as better you can service him. For quality service you need to have tracks about the history of electronic devices, Which 'illness' they have had already. ;)
This gives muc better decicios for a customer when he has to change to a new device. All that. Lot of stuff. My actual system isn't that bad, but it works using VB and VBA and less of queries ( I was working more with keys and looping through recordsets. But this is too slow.
The new system will work in C# and based on sql server 2005. But before starting this, I need to do research about the design. Thx or your help in between

Yeah - I think you store way to much information, or at least talk about storing way to much information given what your system apparently is for.

But as mention - remember to look into what laws there are on data storage of customer information. It is easy to get into trouble.

[dglienna]

We always used Line-Item Detail records for an old system I worked with. Each line of a transaction was specific, and could not be modified. It can be assigned to different owners though. Sounds like you need that for each item you have.

Add the client# to each item. Then you can always split that list if you needed to.

[HairyMonkeyMan]

When we create an invoicing, we write all the details of the invoice (including name and address) to its own table using the current info at the time.

We aren't interested in where the customer used to live, just the invoice.

[JonnyPoet]

We always used Line-Item Detail records for an old system I worked with. Each line of a transaction was specific, and could not be modified. It can be assigned to different owners though. Sounds like you need that for each item you have.

Add the client# to each item. Then you can always split that list if you needed to.Thats a good idea, then I have all the data in a track stored here. Easy to handle. The redundance IMHO in modern systems isn't really the problem,because every history tracking systemIMHO has redundant data.

[JonnyPoet]

When we create an invoicing, we write all the details of the invoice (including name and address) to its own table using the current info at the time.

We aren't interested in where the customer used to live, just the invoice.This seems me to be similar to what dglienna suggested. Great idea, easy to handle.

[Thread1]

have you heard of a slowly changing dimension in multi-dimensional database modelling? perhaps, it is nice to adapt the type 2 or even the extended one. for your reference http://en.wikipedia.org/wiki/Slowly_changing_dimension

[JonnyPoet]

have you heard of a slowly changing dimension in multi-dimensional database modelling? perhaps, it is nice to adapt the type 2 or even the extended one. for your reference http://en.wikipedia.org/wiki/Slowly_changing_dimensionHavn't heard this before but it describes the problem as it is very clearly and shows a good way to go with such a lot of data to track. I'll get a copy of Kimbals book about Data warehouseing I think. Seems to be very interesting

[JonnyPoet]

... It is what is often done with for example price information for a product which in many systems comes with a date to indicates its "active" period so to speak.This is a good idea and IMHO will work.
You do not need a maiden name for this, you can simple change the surname to another value.
I'm not interested in peoples maiden name or if they are married or divorced. No this only should be an example why names are changing for one and the same person.
The problem in reality is the other way round. People are coming to my shop gving me another name then some years ago. So I dont know that they are already in my customers files and new files are created. People thisway gets promotion more then once Unnecessary expenses. So when we find it out we want to correct this butnow there are two different Customer ID's and they needed to be done together into one. This is how we do in the moment. But this is often a problem because of older bills data now have the new name. This is simple out of my understanding of accurate data. accurate data should be able to follow the data and the changes in time.


The name should not be a key of any kind, so it should be a value you can easily change.
The name is no ID but per sure always a key to search a specific person in the system. People are coming to get service, they like it that we already have their neames stroed they give their name and their adress and within a few seconds the whole service order is done as searching by name shows a list with all people who have this name which is sorted by adress so the woman on the phone finds the specific customer in short. But this is totally normal in datasystems.
By knowing the names track you also can have a look on information which saves your business. Think to people you have noticed they dont pay their bills for example.
And this answers your comment:
There is no reason to keep the historical changes, lest you have some actual good reason to do so.

Well, you can also store too much information. If information is only used occasionally, then start question whether it is needed in the system.
This is a good point. I'm using my system for about 20 years now ( I did my first one in 1984 ) and changed it from time to time so it grows more and more along my needs and how the business grows too.
I never stored useless data. But I have seen in the ast years that knowing the track of a person could save lots of money.
Crime and betray grows in an huge amount in the last years. You dont get payment data of people from anywhere. You have to know, if you can trust them. As I told already, you also have to know your enimies.
Also be sure to know the laws on data mining in your country - what you may store, how long and all that. There are different laws for different countries.I never thought about such points. Who can tell you that you are not allowed to store data in your brain ? All the things along your live, cnanybody tell you : forget it ?

All this laws have coming up because of misusage of data IMHO.
So nobody can tell you, you are not allowed to store data about when a person named abc hasn't paid his bill, the amount we are still missing and where this person lives. Got it ? I refuse to deliver Service to such people, when I know they havn't payed my bills 5 years ago. such simple. This are points where the old system leaks and this leaks should be closed in a new system which allowes to look to older files even when customer names are changed.


.... so when the customer changes name - update his account. This is how it is done in the moment. But this way the connection to my book keeping system, which shows me unpaid bills gots lost. ( This is because the book keeping system is a separate program so there is no datatransfer between this programs so I can only look for data there regarding a specific name ) It also would not work without a data timetrack if it would be connected BTW, because the in would not be llowed to simple change the name. This would change already stored financial data! And this is not allowed in our country. So I would need to create a new customerID for the new name in that case.
Similar with marital status - I can't even start to phantom how getting divorced could have any influence on historical data regarding orders.
As told before, this is not my interest. This is only what happenes and where we have to do some dataupdates sometimes, when we get informed. I dont have or want to have the marital state in my system. People have devices and the divices have a customer ID so I know which devices the customer have had serviced from me and the 'illness story' of that electronic devices. (Most times televisions) When a customer calls as told before its a few seconds to have him on the screen and to ask which device is broken. If it is a new one which I dont have in my files we do a new entry myb asking what has happened to his former device. 'People here like to have their 'television service working like medical doktor knowing the 'illness track' of their devices and yo really can give goodadvice to people if you know the device is already 15 years old and hasn't have lots of repairs in between for example.
But this track gets lost when people divorces and one takes the TV and the other takes the DVD recored for example. But as told before splitting this records to two people as we do in the moment brings that we still know about the devices technical problems, but we loose the point if this customer has bought a lot or not. ( Maybe all devices are now at the woman, but he was that one who has bought all the devices ) But now his file is blank. Sure the data are in the financial system, but this is a totally other database which has no connection to that Customer Service system.
- I think you store way to much information
Yes as it is a slowly grown system it has lots of lets say 'muddle' in it in between. Its technically seen consistent, But not really logically.
Understand that I have all that selftrained to myself learned by doing because in the years when I started with my first system 10 MB of data on a harddiscc costs 10.000 U$ and professional datastoring was mostly done on machines bigger then a room. My first PC application worked with an ISAM relational database working in C ( not C++ ) it was years before windows comes up and I had a computerbased scheduling system and a customers database for our customers data.


..., or at least talk about storing way to much information given what your system apparently is for.I'm collecting information about how things can be done giving practical examples to describe some of my needs. There I'm using a field like 'customers' which is broadly known, giving examples which could hopefully easily been understood. But the same problems comes up with devices, which changes in the serialnumbers and the electronic schemas of devices ... Lots of problems needs to use datastoring with a timetrack or as others named this very correct: 'slowly changing dimensions'. Which data I'll track and which I'll only override with the actual data, is a much later decision. At first I have to
a) know which posiibilities are there to be used
b) advantages and disadvantages of the different systems
c) Cost of implementation ( time ) for each item that needs to be stored with timetrack in compare if I don't
d) Estimated time of migrating existing data as version Zero of the new database
d) collecting all relevant data which data really needs to be tracked with its history, which are useless to be tracked and which have shown to be stored in former times but never used.

I've got lots of very informational ideas, like how others store invoice data and this way in a cheap way having accurate data of all the time past, while still having the customers data up to date.

All this will lead me to a design decision.
This will lead to how I will in the end design my new database.

But as mention - remember to look into what laws there are on data storage of customer information. It is easy to get into trouble.Thinking to that point I would think that as long as big firms like Google doesn't have troubles with collecting data, this should not be a problem to store data about devices I have repaired, which service map was needed to be used for the electronic schema; the owners names, adresses and that things. But I know that the laws in the US states are much more sensible about all that then here in Europe. Hopefully this will not change in the next twenty years. ( Hehe I'm 60 now ;) )