How to manage (1 Viewer)

[nirmal]

If there is a lady customer, who is enrolled by her maiden firstname, middle name and last name.
She has a unique national number or ID
Now, after marriage her middle name and last name will change.
Or even after her divorce she re-marries the again her middle name and last name will change.
At times she might change her first name too.
Then how to manage this in a database.
Similarly about the address, where the customer changes the rental place due to change in job or for any reason, then how to change it in database.
Do we have to creat a different address table
Please guide me.

 

[CJ_London]

When you want to keep multiple records of the same thing such as address history , changes in personal data, changes in product prices, etc, you keep a separate table and include a time stamp field to identify when it changed

 

[nirmal]

When you want to keep multiple records of the same thing such as address history , changes in personal data, changes in product prices, etc, you keep a separate table and include a time stamp field to identify when it changed

But what about name, for that too we have to keep a separate table

 

[ebs17]

I think it depends on how and how intensively this old data is to be used later.

=> Some do not want to keep old information and overwrite it in the record.
=> Some only want to keep old data so that they can look it up. It may be sufficient there to keep the old information line-separated in an additional text/memo field in the record. For a simple programmed evaluation of such compilations, one should consider reliable standards of the structure used.
=> If the old data is used more intensively, there is no way around storing the information atomically according to the rules of normalization and thus in additional tables.

 

[CJ_London]

But what about name, for that too we have to keep a separate table

Before you start designing your database, you need a specification of what the db is required to do.

If the specification says you need to be able to search on all names, including old ones, then better to keep in a separate table - perhaps with other related data you need to be able to search on as well - such as who they are/were married to..

If the specification says you need to keep a history of changes that are not easily searchable - perhaps use a memo field as Eberhard suggests

Depending on the specification, a separate table might be what is often called a history table which is effectively the same as the original table but with it's own PK, a timestamp and a FK back to the original table. You have one of these for each table where you need to keep the history. It can even be the same table if you include the additional fields but is a bit more complex to manage.. Or it could be what is often called a changelog table. It is a single table which records the date/time, user, the table (any table) and field (any field within the table) names and the new value. So a user who changes 3 values in table record would generate 3 new records in the changelog table.

It all comes down to what the specification requires.

As a side note, addresses and things like contact methods (phone. email etc) will often have more than one per contact so these are usually stored in a separate table anyway.

 

[nirmal]

Thank You Sir
Actually we have a table which we use to create reports in which we store the customer ID_FK with other data.
Now in due course of time if the name or address changes, then all the previous report which should actually bear the old name or address, now will bear the new ones. So would it not be better to maintain a separate table for name and address with definitely tiemstamp.

 

[CJ_London]

then all the previous report which should actually bear the old name or address, now will bear the new ones.

repeat - depends on the specification requirements - do you want the old name/address on a current report? do you want the relevant name/address at the time of the report? do you want the curremt name/address on an old report?

The specification will then determine how your tables and their relationships are structured

 

[Pat Hartman]

then all the previous report which should actually bear the old name or address, now will bear the new ones

There is no right or wrong with this. I've never had an application where the users wanted old reports to show old names/addresses. They always wanted them to show the current name/address.

 

[CJ_London]

I've never had an application where the users wanted old reports to show old names/addresses

If you include invoices and similar legal documents as reports then they do need to show the original data (in the UK at least) - so I would expect the specification to be to show the relevant name/address at the time of the original report

 

[nirmal]

repeat - depends on the specification requirements - do you want the old name/address on a current report? do you want the relevant name/address at the time of the report? do you want the curremt name/address on an old report?

The specification will then determine how your tables and their relationships are structured

I want the relevant name/address at the time of the report.
So for some reason we want any report on any date, the they should bear the relevant name and address ON THAT PARTICULAR DATE

 

[ebs17]

Then you need your own tables for simple evaluations for the names as well as for the addresses. A summary could (abbreviated) look something like this:

PARAMETERS parDate Date;
SELECT
   P.PersonID,
   N.Name,
   A.Address
FROM
   (tblPersons AS P
      INNER JOIN
         (
            SELECT TOP 1
               PersonID,
               Name
            FROM
               tblNames
            WHERE
               TimeStampField <= parDate
            ORDER BY
               TimeStampField DESC
         ) AS N
         ON P.PeronID = N.PersonID
   )
   INNER JOIN
      (
         SELECT TOP 1
            PersonID,
            Address
         FROM
            tblAddresses
         WHERE
            TimeStampField <= parDate
         ORDER BY
            TimeStampField DESC
      ) AS A
      ON P.PersonID = A.PersonID

 

[plog]

From what you've described you need 3 tables to handle your Customers:

Customers
cust_ID, autonumber, primary key
cust_DOB, date/time, birthdate of customer
other fields that you do not need a history of (gender, blood type, etc)

CustNames
cn_ID, autonumber, primary key
ID_cust, number, foreign key to Customers
cn_StartDate, date/time, date that this name was first used
cn_EndDate, date/time, date that this name is no longer used
cn_FName, text, first name
cn_NName, text, middle name
cn_LName, text, last name

CustAddrs
ca_ID, autonumber, primary key
ID_cust, number, foreign key to Customers
ca_StartDate, date/time, date this address was first used
ca_EndDate, date/time, date this address is no longer used
ca_Addr1, text, address line 1
ca_Addr2, text, address line 2
...city
...state
..zip

That will not allow you to query the customers name and address on a specific date.

 

[nirmal]

Yes Sir
This will take care of all my doubts

Thank You

 

[nirmal]

From what you've described you need 3 tables to handle your Customers:

Customers
cust_ID, autonumber, primary key
cust_DOB, date/time, birthdate of customer
other fields that you do not need a history of (gender, blood type, etc)

CustNames
cn_ID, autonumber, primary key
ID_cust, number, foreign key to Customers
cn_StartDate, date/time, date that this name was first used
cn_EndDate, date/time, date that this name is no longer used
cn_FName, text, first name
cn_NName, text, middle name
cn_LName, text, last name

CustAddrs
ca_ID, autonumber, primary key
ID_cust, number, foreign key to Customers
ca_StartDate, date/time, date this address was first used
ca_EndDate, date/time, date this address is no longer used
ca_Addr1, text, address line 1
ca_Addr2, text, address line 2
...city
...state
..zip

That will not allow you to query the customers name and address on a specific date.

Sir as per my thinking, we have to make tables on similar grounds for
Religion ( as some people change religion)
Marital status
Education
Occupation/Profession
Contact number
Email Id
Spouse details
Sir what about customer's permanent address and Temporary address

Sir any customer who on first enrollment, gives ionformation of their details.
These can be date stamped under created date which will be enrollment date stamp.
Now in future whenever he or she changes it can be date stamped under change date

 

[plog]

Look at my table layouts for CustNames and CustAddrs and you will see a general method (table ID, foreign key to Customers, date and then all the data you want to track changes of). Use that general method for all the tables you want to track data that can change.

Upon reading your first post I thought this was over-engineering a database but gave you the benefit of the doubt. But now you want to track everything in the world and it just seems unnecessary. Why do you care what a person's profession was 4 years ago? What does that do for you?

 

[nirmal]

Sir, actually I am designing a database for medical professional. There they require to maintain all these records.
Your point of profession (and education) I accept. But other things do matter.
Sir, here they have to generate reports of back date if asked for.
Thats the reason they have to maintain all the past details.