Dealing with deleting in Parent/Child Tbale relationship

[RFH Reseller: JohnnyW]

Just wondering if anyone had a good way of dealing with deleting a record in a parent child relationship in a database.

For example say you had

tblCars
CarID
Manufacturer
Model
ColourID

tblCarColour
ColourID
ColourName
RBG

If say the manufacturer stopped making a certain colour so you want to remove the colour so you can't order the car in that colour now. How would you deal with that?

If you deleted it obviously the historical data would be messed up as some orders would probably contain an order for a car in that colour.

Just interested to see if anyone had a good way of dealing with this sort of problem.

[RFH Reseller: Wise Webs]

Just wondering if anyone had a good way of dealing with deleting a record in a parent child relationship in a database.

For example say you had

tblCars
CarID
Manufacturer
Model
ColourID

tblCarColour
ColourID
ColourName
RBG

If say the manufacturer stopped making a certain colour so you want to remove the colour so you can't order the car in that colour now. How would you deal with that?

If you deleted it obviously the historical data would be messed up as some orders would probably contain an order for a car in that colour.

Just interested to see if anyone had a good way of dealing with this sort of problem.

I would add a field to tblCarColour called Discontinued and set it as a boolean with a default of false.

When the colour is discontinued just flip the flag to TRUE.

HTH

Harry

[Staff: Warren Ashcroft]

I would add a field to tblCarColour called Discontinued and set it as a boolean with a default of false.

But then that would Discontinue the colour for all cars that use that particular colour?

[RFH Reseller: JohnnyW]

But then that would Discontinue the colour for all cars that use that particular colour?

Sorry that was probably my fault, pretty crap example.

At the moment I use the above solution of having an Active/Inactive boolean field but it just doesn't sit right with me, was just wondering if anyone else had a better way of doing it.

The problem I have at the moment on a project is with a status field which uses a similar system at the moment to my example. Admin on the site want to be able to delete status's and create new ones etc but at any time a user can update their profile (in 5 years time for example) and if they had select the status which has then been deleted then it causes problems.

Hope that made sense

[RFH Reseller: Wise Webs]

Sorry that was probably my fault, pretty crap example.

At the moment I use the above solution of having an Active/Inactive boolean field but it just doesn't sit right with me, was just wondering if anyone else had a better way of doing it.

The problem I have at the moment on a project is with a status field which uses a similar system at the moment to my example. Admin on the site want to be able to delete status's and create new ones etc but at any time a user can update their profile (in 5 years time for example) and if they had select the status which has then been deleted then it causes problems.

Hope that made sense

A boolean flag would sit fine in my view then. If a user has a profile with a status 'A' then admin could 'delete' the status (ie set the discontinued flag as 'true'). All users with a profile of this status would see their profile as Status 'A'. Any other user wouldnt be able to select status 'A'.

HTH

Harry

[RFH Reseller: askjim]

I totally agree with the solution, deleting data causes so many headaches - in reality you end up keeping it but marking it flagged as deleted.

A boolean flag would sit fine in my view then. If a user has a profile with a status 'A' then admin could 'delete' the status (ie set the discontinued flag as 'true'). All users with a profile of this status would see their profile as Status 'A'. Any other user wouldnt be able to select status 'A'.

HTH

Harry

[dbuckle]

I totally agree with the solution, deleting data causes so many headaches - in reality you end up keeping it but marking it flagged as deleted.

It's quite handy to have a stored proc that properly deletes all the parent/child data, at least for dev purposes.

[RFH Reseller: nick]

This is a many-to-many relationship, the way you would normally deal with something like this is to use three tables.

tblCars
CarID
Manufacturer
Model

tblColours
ColourID
ColourName
RBG

tblCarColours
CarID
ColourID

Then you just add or delete entries from the new tblCarColours table. If possible you would want to add a foreign key constraint to the car and colour id's in this table so that the related rows would be deleted if an entry is deleted from either of the other tables.

[Gilgamesh]

I usually add these fields to all important tables, just out of habit now

[CreatedDate] datetime,
[LastModifiedDate] datetime,
[Active] bit

These have proved useful on so many occasions when the spec changes!

[Staff: Warren Ashcroft]

I usually add these fields to all important tables, just out of habit now

[CreatedDate] datetime,
[LastModifiedDate] datetime

Ditto, would be great if SQL Server natively/internally supported these values for each record.