and adds several dozen other complications in its place. The rare change of a userid is far easier to handle than all of the problems that adding an unnecessary id can cause. Plus adding the extra field means that the data is no longer properly normalised since tnot all the fields in the table have the same relationship to the key and nothing but the key any more.
The only time you should be adding extra numeric keys to tables is when they have no natural key, when the natural key is too long to be used, or the key value changes too frequently (say twice or more since you started reading this).
I’d be more inclined to have a numerical ID in the event changing a username is an option (now or in the future). It removes the PK dependency on a changeable item then.
And the easiest way to contruct the table if you have one unique field is to make that the primary key (unless the length of the field is such tat it would be totally impractical to have that as the primary key). In the case of user names you will generally have a maximum length that they can be which will be small enough that having that as the primary key will be practical and adding an extra meaningless number field to perform the same task of distinguishing between users that the username already does will not be necessary.
No need to reinvent the wheels unless you want to for fun. Not sure if this fits your requirement but I would start from existing model and change accordingly.
felgall explained it with some big words, but super-generalizing-and-simplifying, you want to prevent having redundant data in your database.
You seem to already have a grasp on that when you pointed out the redundancy there would be in having the role and privilege level in the same database.
Also, any time you are considering having a comma-separated list (such as, creating a comma-separated list of user-roles), that’s a likely indicator that you need another table (comma-separated lists almost always equal bad).
And if you want to prevent two users having the same name, you just make the field be “unique” (which is what r937 hinted at. ;))
What if, as we were thinking, I would use an integer to specify the role of a person? This would work in a “pyramidal” hierarchy right?
Also with the data specified presumably only one user can have a given username and therefore that field ought to be the primary key and the id field can be dropped (unless usernames that are hundreds of characters long are allowed).
You are right, I put an id field because I wanted to avoid the problem of two users having the same name, but what are the odds of that ^^
Normalising the database will tell you exactly what tables you need and what each should contain. The only time you would construct your database differently from what the normalisation rules dictate based on your fields and the relationships between the fields is where you specifically decide to undo a normalisation for efficiency reasons with respect to sections of the data that are relatively static but which are frequently read.
How that would apply to the particular fields referred to by the OP depends on whether a given user can have more than one role at the same time. If so then a second table is needed so that there can be a record in that table for each role. If not then only the one table is needed.
Also with the data specified presumably only one user can have a given username and therefore that field ought to be the primary key and the id field can be dropped (unless usernames that are hundreds of characters long are allowed).
The only situations I can think of when you will need 2 or more tables are
1 - where a user can have more than 1 role
2 - where you have a role validation table in which fldRoleId is the primary key in the role validation table and fldRoleId in the user table is a foreign key to the validation table.
Right. If you implement the roles as privilege levels you would indeed a second table that ties the roles and their corresponding privilege level together.
Right again
If the roles are all incremental that’s a nice system.
If roles are not linearly ordered like in your example then you would of course need another system.
Let’s think about this example now: if, instead of a number, the privilege field would have been a String (example: “manager”) then I probably would have had to create a second table to connect the string to the corresponding privilege level, right?
Yes, but in my example a user can have more than one role without having to create a second table
I don’t actually have the book, but if he’s using two tables then a user in his example can probably have more than one role at the same time. In your system this doesn’t make sense. If a user has roles 2 and 4 for example, 2 is redundant because that’s already implied by role 4.
Hence, a user can only have one role at the time and you could put the field for the role in the users table.
Yes, just because in the book by Kevin Yank “Build your own database driven website using PHP in MySQL” there is an example in which he splits the db this way, and I though that it was better to ask why somebody would want to do such a thing
Every user needs to have a role right? If so, I don’t see why you would split up the table and I would use just one table.
Any reason you thought splitting it into two tables would be better?
I’m saying that username takes up more memory to store, since the average length of a username would be longer than 4 bytes, thus it’d take up more memory.
In most smaller cases, it’s not that big of a deal. However, if you’re dealing with a database that has millions, or more, entries, you could be looking at a lot more storage space needed…
Another benefit to using a numerical key is that it takes less memory to store. If you are using a string as a key, you are probably using many times more data then you need. Each character takes about a byte, so if you have strings that are longer than 2 or 3 characters it’s going to be much more memory than an integer type.
It could add up to a lot. If you’re using an unsigned int (4 bytes), you can have 4294967296 rows. If you use UTF-8, you could technically store as many rows for the same memory with four characters… but that’s highly impractical. More likely, you’d allow between 4-24 characters, 14 being the average. That means you’re using about 10 bytes for every single key use… that could REALLLLLYYYY add up if you have many rows.
