Design - duplicating properties vs not allowing delete

Question

I'm designing a database for a mobile app CMS that contains user data. These objects are Address and Job (a job can have 1 address and an address can have many jobs). The first version of a similar project basically inherited the address properties in the job, which would allow the user to delete the address while maintaining job properties. This essentially results in duplicate columns (both objects have street_address, city, state/province, country).

I'm curious about input as to whether the better design and experience is to either not allow object deletion or use a flag to indicate delete status and basically hide those from the user. This would allow the job to maintain its address foreign key even after the address is "deleted".

Answer 1

There are a few issues that come to mind.

• A normalized approach (jobs in one table, addresses in another, related only by a foreign key) is desirable from a purely database design perspective. It makes both storage and reporting more efficient.

• With this normalized schema, however, you'll want to make sure that an address isn't entered in duplicate (for instance when creating or changing a job). This could typically be the task of a "create job" stored procedure, but you'd have to build some sort of matching logic to merge addresses like 101 First Street with 101 East 1st St, etc. This logic may well be in the GUI, in the form of an "auto-complete" dropdown or similar.

• I'm guessing that there could be legal aspects as well: there are juristictions where a person (like a customer) can request that you remove any personally identifiable information about them after your relation ends. If that information includes that you've done business at their street address, then you'd probably want to ask your legal beagles about the legality of retaining a record of addresses even though you've deleted or masked the job data.