database normalization do I need it

Question

I've read a lot about normalization and I still can't fully understand it/I'm not sure how to normalize this. This is how my database currently looks, does it even need to be normalized? If so, where do I even start?

http://i.imgur.com/L43fHS6.png this is what it currently looks like

If you are unable to get the data you need from a single query because you have too much data in one cell or you have split data along columns rather than rows you need to normalize the data. What you have looks relatively fine as long as you don't need to access a individual user_ID with ease. This would also stop joining of tables on user_IDs so it's your call — Matt, Apr 29 '16 at 16:27
See [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — Alex K., Apr 29 '16 at 16:27
Well I do need to access individual user_ID within a certain time period (start_timestamp, end_timestamp), in that case how should I even normalize this? Even after reading up on it I'm still lost. — Theiq, Apr 29 '16 at 16:28
What does each of those rows represent? Whatever it is, you want a that_users table where each row references the row the users were on, and one of the users that were on that row. — Uueerdo, Apr 29 '16 at 16:35
This table is in desperate need of normalisation. It can be a daunting concept when first presented with it, but actually it's a very simple one to comprehend. If I can grasp it, anyone can. — Strawberry, Apr 29 '16 at 16:41

score 0 · Accepted Answer · answered Apr 29 '16 at 16:42

You will want to have 1 row per user_ID so you can easily access all the data.

e.g. for your gameID 5002947 (row11) this needs to be split into the following:

id       setup_id   user_ID
5002947  997        563749
5002947  997        500243
5002947  997        536271
...

You have two options. Create a complex SQL query that will handle this (I can't supply this unfortunately but I'm sure others could) or use php.

PHP method

Select all rows and explode the userID into an array. loop through this array and insert back into the database. Depending on the number of rows and userIDs you have this may take a while to execute.

e.g.

$mysqli = new mysqli(HOST, USER, PASSWORD, DATABASE);
$query = 'SELECT * FROM table';
$data = mysqli_query($mysqli, $query);
while($row = mysqli_fetch_array($data)) 
{
    $data[] = array("gameid"=>$row['game_ID'], "setupid"=>$row['setup_ID'],"userid"=>str_getcsv($row['user_ID'])); /*And all your other information*/
}

for($i=0; $i<count($data); $i++) {
    $gameid = $data[$i]['gameid'];
    $setupid = $data[$i]['setupid'];
    /*Other info you need*/

    for ($x=0; $x<count($data[$i]['userid']);$x++) {
        $userid = $data[$i]['userid'][$x];
        if ($stmt = $mysqli->prepare("INSERT INTO newtable (game_ID, setup_ID, user_ID) VALUES (?, ?, ?)")) {
            $stmt->bind_param('iii', $gameid ,$setupid ,$userid);
            if (!$stmt->execute()) {
                $stmt->close();
            }
        }

    }
}

database normalization do I need it

1 Answers1