is this a good table structure?

Jack_McSlay

I'm not asking for help, just curious... I'm going to do stuff for my university and I was given a quite... peculiar user table to work with

user_code varchar(6) primary key <- this thing is aways numeric
user_login varchar (8) unique (not sure if this one was actually indexed)
user_name varchar (30)
user_password varchar (8) null <- they're storing the plain passwords here!
user_type varchar(30) <- only a few different values can be set to this one
email varchar (30)

to my concept the right way do do this would be something like this

user_code mediumint(8) unsigned primary key auto increment
user_login varchar (20) unique
user_name varchar (50)
user_password varchar (32) <- encrypted
user_type enum or tinyint
email varchar (30)

am I right to find this freaky or it that one actually ill-conceived?

Roger_Ramjet

Most definitely yours is an improvement.
Primary keys should always be one of the integer types - int indexes are so much faster. And they should never have meaning outside the db so autoinc is the best way to go. Sorry if you know all that already, no offence meant or taken I hope.

When you say 'for my university' do you mean as part of your course?
Whoever designed the original is an amateur. Sounds like the guy we had 'teaching' databases on my masters program - an industrial chemist who had jumped on the IT bandwagon. One guy who had been doing dbs in industry for over 6 years got a fail untill he sat down with the lecturer and explained how and why his assignment worked.

Jack_McSlay

just confirming, actually, since my current experience with DBs come from only, well, experience. and it seems that since my first contact i've already figured what you just told me

I'm currently working as an intern on my university and got involved in a project which i got asked to create another table only if necessary, and believe it or not, that is the table they're officially using on the website as user table for the students to view their grades, and unfortunately the one I'll have to use... why such table got accepted, I don't know

Roger_Ramjet

I do. Everyone is an IT expert these days. Because there are so many GUI tools around to create dbs and tables, no-one has to learn sql before they can get started, so they don't bother to learn the theory either. They baulk at paying me the price for my decades of experience, thinking it is all so easy. Then their data disappears into a black hole database schema, or spits out the wrong data without anyone realising untill it is too late, if they ever realise at all.

justsomeone

Yours certainly looks like the way thngs ought to be done - but are there "real world" compromises here?

For instance, should your database actually generate the user codes or are they generated by a master system somewhere?

Could it be that your assumption that user codes are always numeric is flawed - that for certain users you need to allow for alphas in there?

If you're absolutely certain that the asnwers to the abcoe are all "No", then your autoincrement is better than the original.

As regards password encryption, it's better to store encrypted, but that means that absent minded users can't just ask the admin person "what's my password?" - admin now needs to reset the password, or ideally the user needs to do this themselves. This is extra development.

Roger_Ramjet

Justsomeone, the user should not be able to reset their password without first entering the existing one, so that rules out doing it themselves if they've forgotten it now doesn't it. Now, the admins should also not be able to just look up anyones password, so encryption is a must. All they should be able to do is reset a user's password, and even then it should probably not be just any admin. Anything less is a serious security flaw for the whole organisation. Just imagine some lowly admin, possibly part-time or a temp, being able to access the managing director's account, or wander unhindered through people's personel files :glare:

Primary key should still be autoinc with a seperate indexed column for user codes. Primary keys are for internal database organisation only and should never have any meaning outside the database, or be revealed to anyone other than the db admins.

justsomeone

Hello Roger,

Ease up on the attitude, eh?

Now taking your points in turn...

Most established organisations would disagree with you about users not being able to reset their password without knowing the original one. This is particularly true of organisations with large userbases. After all, how many "lowly" admins do you think Google would need to handle forgotten passwords for their userbase. That's why most systems operate a "Forgotten your password?" feature - sometimes as simple as sending a "reset your password" link to the the registered email address, although more advanced ones use a CAPTCHA system in conjuction with some Q&A to determine that the person requesting the reset link is indeed a person, and that there is some reason to believe that they are the person they claim to be. I just metion this because in the real world you probably don't want admin staff to have to be involved in password resets - although there are always exceptions.

On the other hand, I absolutely agree that it is far, far better to have passwords encrypted than not. I just wanted to make the point that this introduces extra overheads in developing, testing and maintaining the extra screens required to reset the passwords, whereas a bog-standard tool could be used to query the current password. Again, whether this extra development is worth while is for whoever manages resource in the organisation to decide. For a confidential and/or critical system this is probably absolutely required, for a softball league system they may decide to pass and use the development time on something else. There are generally many other projects competing for people's time.

Again, I accept that you are generally right when it comes to primary keys, but as ever there are exceptions. For example if you have a table of state abbreviations and names, I don't see what advantage an autoinc primary key will have over using the unique two-letter abreviation as your primary key. In the original case, we don't know what the history/use of the user_code is. maybe it is a foreign key to some external system (perhaps even an offline filing cabinet) , in which case you probably want to leave it alone and add a new field as your primary autoinc field.

I guess the main general point I was trying to make is that if you come across bad design "features" don't just bin them. Take the time to find out why those decisions were made. They may have been undesriable but unaviodable compromises. Also take the time to understand who uses these features and why. If after all of that, you are still certain that there is no reason for the poor feature to remain, and no impacts are caused by its removal, then you can go remove it.

Roger_Ramjet

EH! What 'attitude'?

We are, here, talking about an in-house system at a university, not some web app with millions of anonymous users. These are two totally different things. My idea of an 'established' organisation is not google or myspace and the like, it is Bank of America, Stanford University, the Department of Defense and the like; and you can be sure that they do not use automated 'reset your password' email links, not by any means.

OK, so in the special case of your american abbreviations for states the advantages of integer indexes would be offset by the join required to get the abbreviation. However, if you are going to use a join on the abbreviation to get the full name of the state then that does not apply, the autoinc will win every time.

justsomeone

Hi again.

By attitidue, I just mean that I took mild offense at phrases such as "now doesn't it", "lowly admins" and glaring smilies (if that's not a contradiction in terms)... but maybe you didn't mean anything by it. No matter.

Like I said, I don't disagree that everything you've said is usually correct. I'm just advising against making a rash decision to throw anything out just because it would appear to be wrong.

Roger_Ramjet

Context mate, context. 'lowly admins' in relation to the MD, or Vice Chancellor, or even senior IT staff cos reset password is going to be done by the lowest rung on the ladder, generally. And don't take offence at that, I've been a contract network support many times and that is what happened even when I was the most skilled, knowlegeable and experienced person there I was still just the contractor.

justsomeone

Don't worry, I wasn't taking "lowly admin" to refer to me. I just don't like the term.

Hopefully between the two of us, there's plenty of food for thought for Jack