Quote:
|
Originally Posted by nineinchnailgun
I run a website that stores a lot of user-generated data. Currently, I allow them to modify records. Say someone rates a product as 3 out of 5 stars today. Tomorrow, they can change that to 5/5. When they do so, the record is simply updated.
I'm thinking that I'd rather have all of the data - to know the data's history - and I'm considering different ways of doing this. One way that I'm considering is to use a "pointer" (for lack of a better word). I'd insert the new rating into the table, snag its ID, and then update its parent to add the child's ID into its previously null "pointer" field. An example of one item that has had its rating modified is below:
Code:
ID | Product | Rating | childID
1 | Flowers | 3 | 2
2 | Flowers | 5 | NULL
The reason I like this approach is that the freshest data can be isolated by searching for all records where childID is null. However, are there other general approaches to data persistence that might be more efficient? Any feedback would be greatly appreciated. |
Hey,
this is the same as the solution that that I came up with for my project. What you do is use a reflexive relationship to track the previous values of rating, i.e. a kind of a singly-linked list. Null indicates the latest rating.
I am going to use it to keep track of the order in which tasks need to be performed.
I am not an sql guru like most of the guys here, so I couldn't give an advice based on my experience, but the following
article is by a bloke who seems to know what he is doing.
The only problem I think is that when done this way, the table is no longer in 3NF because all non-prime attributes will depend on on Id as well as ChildId, which violates the "nothing but the key" rule. In my design I have:
Code:
table 1:
ID | Product | Rating |
1 | Flowers | 3 |
2 | Flowers | 5 |
table 2:
ID{pk, fk->table1.ID} | ChildId{fk->table1.ID)
Best