Linked Questions

15
votes
3answers
8k views

Implementing versioning system with MySQL

I know this has been asked here and here, but I have the same idea with a different possible implementation and I need some help. Initially I had my blogstories table with this structure: | Column ...
10
votes
2answers
20k views

Storing a Billing Address Best Practice in Orders Table

Can someone help me understand this user's answer for a CustomerLocation table. I really want a good method for storing addresses in the orders table. What I'm looking for is how I can set up my ...
5
votes
6answers
2k views

Storing personal data on an invoice to preserve data integrity

I want to store invoice data like addresses of people. But the problem is addresses can change over time - for example someone will move out and change his address. The problem of storing such data ...
4
votes
3answers
3k views

SQL - Many-to-Many relationship with same table and with relationship constraint

I have a SellerProduct table. Each row within the table represents product information as offered by a seller. The SellerProduct table has the following columns: id (serial, pk) productName (nvarchar(...
5
votes
3answers
544 views

Storing two integers as a decimal

What are the downsides to storing two integers as a decimal? I am storing asset details in tables, each asset type has its own table (each asset is very different) and using another table to define ...
4
votes
3answers
2k views

Best way to model a single user with multiple usernames

I'm looking to write a website where a user will login with their email address and password, but can have multiple usernames. The context is a social gaming website. This may result in a user ...
2
votes
4answers
1k views

Auditing data changes in SQL Server

I am tasked with implementing auditing of data changes on our database. I am aware that there are commercial products for this, but I wanted to know which design would be better. For any given row ...
0
votes
2answers
2k views

Best way to store deleted rows

I have a table with operations that can be deleted by users. What happens, is that I want to store deleted rows for some time, to serve as recover in case any client regrets the deletion. One of the ...
3
votes
3answers
301 views

Design of portable tables with validity interval (historization, temporal databases)

I'm designing a data model for an application which must keep track about the changes of data. In a first step, my application must support PostgreSQL, but I'd like to add support for other RDBMS (...
2
votes
1answer
1k views

Database design how to track a change on a patient row and set in a log table

I need to track any update on my patients table. The table contains the following fields: Patient ID; Name; address; phone relative name; relative phone smoker; alcoholic. Now I need to track if a ...
1
vote
0answers
150 views

created_at, modified_at, status - is this a documented pattern?

I am but a lowly code monkey, so this may be a very basic question. For the current project, our architect has required that all tables, without exception, must have at least these four fields: `id` ...
1
vote
1answer
44 views

Is it a good to move data out of the original table when it's deleted or archived?

I have a table with offers where I perform typical CRUD actions. Each offer can has one out of the following statuses: active, deleted, archived, withdrawn, etc. Most actions I perform only for ...
3
votes
0answers
57 views

Versioning rows in a table

So I have this concept in my head of "immutable" records, which I am sure is not novel, but can't seem to find the right google keywords. Basically, historical row values need to be preserved as '...