Normalizing MySQL database with multiple values in one column

Question

I'm a little stuck for ideas of normalizing my database.

Some information about my project: I'm creating an reporting tool with PHP and MySQL for security guards. Every security guard has own login and can create a report of the day. The report consist of these inputs:

Date
Place
Guards at work - Here is my problem
Situation - Here is my problem also
And then there are multiple input fields where there are numbers that will be used for charts. (like how many people you threw out today etc)

Now, the guard can make a report but when it comes to adding MORE guards for one report I lose my head. I've read about concat and splitting with ',' in same column but that destroys the normalization and is a bad practice.

You can see this pic of report:

Now this is in Norwegian so maybe you don't understand. "Vektere på Jobb" means Security Guards at work, here I must be able to add multiple guard names and then save this to my DB.

The problem: I have multiple input fields for other stats so I can create a chart, but I can't add more than one security guard. And if I should create a new line, doesn't that create a new ID and columns with values that need to be the same, just add another security guard?

Hope you understood my problem, just give me an idea of how this can be done and I'll take care of the rest.

Another pic that illustrates what I need: but this is not possible (breaks normalization)

(The Graph columns are numbers so I can create a chart)

Ive red about concat and splitting with ',' in same column but that destroys the normalization and is a bad practice. I think that is the heart of the problem. You should, I feel, go back and rethink your entire database schema and look at using additional database tables. — Abela
– Abela, Commented Sep 25, 2016 at 16:56

M.Alnashmi · Accepted Answer · 2016-09-25 16:51:25Z

3

You can create a third table that acts as a "has many" guards.

So for example: Report_guards

With 2 columns, report/graph is, guard id

Both columns are foreign keys to the respective table.

answered Sep 25, 2016 at 16:51

M.Alnashmi

5921 gold badge5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Asim Over a year ago

Like a Junction table?

Asim Over a year ago

Im going to work with this tonight and try to make it happend. If im unable to do it ill come back here and ask for help again if possible. Thanks guys for your time those who answered :)

Collectives™ on Stack Overflow

Normalizing MySQL database with multiple values in one column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related