CODEWITHSUNDEEP
sql-serverdatabase-design
Guang
Guang

Reputation: 43

How to design db tables that store multiple criteria that describe something meaningful?

I was trying to design tables to store settings like giving out rewards. The reward is given need to be limited based on some criteria. For example, Children with 2 stars and 25 points is rewarded 20 candies while children with 2 stars and 70 points is rewarded 65 candies.

Here is what I can think out of:

  1. Design a table to store all information in one table like table below:

table 1

  1. Design it as separated table:

table 2-parent

table 2-child

  1. For the first approach, it is easy to find the data I need based on the criteria but if the criteria is too long, the column can take it and it will be harder for the system to separate the data.

  2. For the second approach, maintaining is it easy but the parent table will become meaningless if the child does not exist. It is because they are depending on each other.

I would like to ask which is the best way to design the table for this approach. It should be ease for searching and maintaining. Is there any better way to do so?

Upvotes: 0

Views: 85

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

If you don't ever need to look at "stars" or "points" individually, then Design 1 is the better approach.

If you do need to look at these criteria individually, then Design 2 is absolutely the better approach.

Upvotes: 1

Related Questions