Database Design – SQL Schema Design Advice

database-design

This question contains no concrete programming question, i would only like to hear some ideas, on how it would be appropriate to design my database.

So recently I started designing an application where the main goal is the following:

There are users
The users can create reports (they some get points for creating one)
They can provide additional data (additional points)
They can view others reports
They can unlock additional data about other peoples records, using
their own points

The point of my question is: How to store and/or calculate the users current points?

1. First method

The schema, and points-calculating method I first thought of looks like this:

schema

The users (can) have reports, the reports (can) have report details, which are connected to a report detail type, which contains the information about how much points it costs to unlock, or to create a detail from a given type. The user also (can) have transactions which contains the unlocks by the user.

From this schema i could create a query which returns, how much score the user have created, and how much he/she has spent with transactions. I could the substract the two, and i have the current score.

The problem with this: If the score for one report detail type gets modified the score for all users will be modified. Which i don't want to.

2. Second method

The second idea i had is to simply store the users points in a column in the users table, and whenever he/she tries to create/unlock something, update it. The schema could be identical, but there would be an extra column in the users table. For some reason it feels really unprofessional, and it also feels like, that i would store data, where it doesn't belong.

3. ???

I would really appreciate any ideas, on which one of these would be better and why! If I'm started thinking about the problem in a completely wrong manner, I also would like to know about a better solution! Thanks for any help!

Best Answer

The third option is to keep a transaction log of points transactions.

Use the schema from your first option to record the "going rate" for points. Whenever you have a transaction that either gives or takes points away from a user, write a record (or multiple records) into a points transaction log table.

The transaction log table will have a FK to USERS and a date/time to indicate when the transaction took place. It could also have FK's to the items which generated the points, such as FK's to your TRANSACTIONS and REPORT_DETAILS tables, that's up to you.

Most importantly, the transaction log table will have a column for the number of points awarded(+) or spent(-). To get a user's point balance, you run a sum of all transactions for the user up until the current time. The important thing is to keep a record of how many points were given or taken for that user, at that time regardless of what may happen to other users or at other times.

If performance becomes an issue, you can keep the user's current point total on the USERS table, but this should be denormalized data based on the transaction log details. If you do this, then you need to have controls in place to make sure you're managing this redundancy appropriately.

Related Solutions

How to design a schema to store search keywords

First, let me address your concern about data redundancy. I agree with you that the second schema is more likely to reduce the redundancy, and is probably closer to what I would go with. One thing to be aware of, though, is this will be unique search terms. So unless you have some way of normalizing data, misspellings will get through and be viewed as different search terms.

Also, I would switch the way you link the searchTerm to the date searched, so that dateofSearchTerm links to the search term (not the other way around).

CREATE TABLE SearchTerm (
 ID             INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 SearchTerm     VARCHAR( 255 )  NOT NULL,
 Hit           INTEGER         NOT NULL,
);


CREATE TABLE DateofSearchTerm (
 DateID         INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 searchID       INTEGER         NOT NULL,
 Date           DATE            NOT NULL
);

It would also be a good idea to make searchID a foreign key that references searchTerm.ID to ensure there are no orphan rows (rows in DateofSearchTerm that do not have a matching parent in SearchTerm).

As for your anecdotal story about your friend, the biggest thing I see you missing in your designs are indexes. A table 3-million strong should have no problem running quickly with proper indexes.

Using your design, you would want an index on the searchTerm.searchTerm column, and on DateofSearchTerm.date.

I think if you follow the advice to make searchID a foreign key, that will also become a proper index. I don't use SQL Server, so I don't know if foreign keys require an index, so make sure that it does.

Mysql – How to design future proof tables

You can't design a schema to cope with every possible eventuality, so don't try. Couple of options:

Design for what you know now. Revisit and modify when new requirements emerge.
Use an EAV model to extend the entities which require a flexible property bag of additional fields. You can use a traditional normalised model for the common/base attributes and EAV for expansion.

For a use case such as yours there's a good argument for reading the user profile attributes once per session and caching until exit. This avoids the major EAV caveat, crappy performance.