5

Ok I recently asked a question and users answered is that I should normalize my database but I don't think I should really do it..

The logic goes like this

Am storing scripts in the database which are executed dynamically according to the user..

So for example there's a script table

script_id | script_name
+----------------------+
12345       demo1
54462       demo2
90874       demo3
43058       demo4

And now the user table

allowed_script_ids
+-----------------+
21345|90874

So this is simple here, but what happens is if I delete say script_id 90874, but it doesn't remove the record from the user table so they suggested me for normalizing the database, but what if user has access to 1000 scripts? do I need 1000 records for that? Or I should continue with the way am going? Even If I insert each record entry for each access, I need to delete those everytime I revoke the access for that user.

Random Guy
  • 2,878
  • 5
  • 20
  • 32
  • Yes, if the user has access to 1000 scripts, then that's 1000 entries for that user in the `allowed_scripts` table. – halfer Jan 15 '13 at 12:27
  • Keeping with the 1000 scripts idea, you'll end up hitting row size limits if you don't start normalising I reckon. – Jonnix Jan 15 '13 at 12:27
  • Take a look @ [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. Jan 15 '13 at 12:28
  • Are you able to describe those 1000 scripts with a category? That would negate needing to set up permission to each of them individually. – halfer Jan 15 '13 at 12:29
  • @halfer that's too much, each user, insert entries on access, remove entries on revoking the rights, and what if there are 100 users for this? 100x1000, doesnt make sense to me – Random Guy Jan 15 '13 at 12:29
  • @RandomGuy - aha, I misread the format of the `allowed_script_ids` table. I thought that was a user<->script many:many relation, which sounded fine. Yes, don't have a concatenated list - put `scripts` in its own table, and have 1000 entries as I described in a join table between `users` and `scripts`. – halfer Jan 15 '13 at 12:30
  • Can scripts be used by multiple users? The only reason you'd need a `userid/sriptid` join table is if that were the case (or if you were only going to store unique script entries). – Brad Christie Jan 15 '13 at 12:33
  • @BradChristie yap they can be accessed by multiple users – Random Guy Jan 15 '13 at 12:36

3 Answers3

6

1000 rows are not a lot (in the same light, nor is 10,000,000), normalising your database (ie. associating users to scripts) is perfect fit for this. If you're concatenating a string, unless you're using TEXT (which is baaaaad for this anyway!), you're probably going to hit some form of field length limit before you can add too many script IDs anyway.

So yes, I would also suggest that your normalise to this extent:

Script

script_id
name

User

user_id
...

User_Script

user_script_id
user_id
script_id

Visualisation

.. each relationship will then go into User_Script.

This is much cleaner than concatenating a string, and upon deletion, seeking/replacing from a string. It will be faster, cleaner, and help you actually see your database in a much more streamlined way.

Currently, how would you get all of the script names out of the database? With the above somewhat-normalised design, you can run this query similar to this:

SELECT `user`.`first_name`, `script.name` FROM `User_Script`
INNER JOIN `Script` USING (`script_id`)
INNER JOIN `User` USING (`user_id`)
Rudi Visser
  • 21,350
  • 5
  • 71
  • 97
2

I would normalise that data, yes. A third table that links users against scripts is what you should do.

If a user has access to 1000 scripts, sure that will mean 1000 entries in this new third table, but that's fine. You'll find that the data is easier to manage, and if, like you say, a script is removed, it's trivial to also remove its entry in this 3rd table, without having to resort to awkward substring routines in a delimited field in the users table.

Don't worry about the number of rows this will produce in the new table, MySQL is perfectly capable of handling millions of rows in a table with ease, and if you create decent indexes, it will allow you to join those 3 tables extremely quickly, probably more quickly than performing substring matches as you'll have to do now.

Ashley Sheridan
  • 526
  • 3
  • 6
  • 1
    @RandomGuy - Aren't 1,000 scripts in a single record a bigger mess? – Álvaro González Jan 15 '13 at 12:32
  • No, it won't end up a mess, t's actually the preferred pattern for this sort of data model. For example, querying for all scripts that a user can run would look like this: SELECT * FROM scripts s LEFT JOIN allowed_scripts a ON a.scriptid = s.scriptid WHERE a.userid = 12345 – Ashley Sheridan Jan 15 '13 at 12:32
  • @ÁlvaroG.Vicario I pass the id only with the separator – Random Guy Jan 15 '13 at 15:19
0

You could use a status bit to indicate access for the use. By this way you are getting the advantage of quick access with an index of unique script id , and for updation all it takes a resetting of your boolean bit

For example

    user access
    script_id user_id isGranted
    ----------------------------
    21345     some_user_id yes

And, your original idea of using a string and sift through it for possible scripts to which the user has access to might be inefficient if you have to constantly give or take access to a script based on the user's preference

consider this 

    12|34|45|56|

what if you have to grant or revoke access to script 56 repetitively . . . .you cant retrieve and process strings for this repetitive action which is needless and also inefficient. In that case, setting a boolean bit helps and saves a lot of processing trouble

palerdot
  • 7,416
  • 5
  • 41
  • 47