Data-Type for User-Roles?

[TomTees]

I have been persuaded to create a more generic table called "User" and add a column called "RoleType" to distinguish between 'Admins', 'Visitors', 'Customers', etc.

My question is, "What Data-Type should I use?"

I tend to prefer verbose data-types because looking at a column with integers in it tells someone else looking at an isolated table NOTHING!!

By contract, seeing 'Admins', 'Visitors', 'Customers', etc. in a column is pretty self-documenting when the column is called "RoleType".

The only down-side is that it is more of a pain programmatically to have to look for a string versus an integer value.

What do you think?



TomTees

[r937]

The only down-side is that it is more of a pain programmatically to have to look for a string versus an integer value.

i don't buy that argument at all, sorry

there is no "more of a pain" writing

WHERE roletype = 'Admin'

as compared with

WHERE roletype = 3

go ahead and use a VARCHAR instead of an integer

[TomTees]

i don't buy that argument at all, sorry

there is no "more of a pain" writing

WHERE roletype = 'Admin'

as compared with

WHERE roletype = 3

go ahead and use a VARCHAR instead of an integer

Glad you agree with me on wanting to use something self-identitfying!!


TomTees

[felgall]

It would be more of a pain using numbers since then you'd need to be constantly looking up what a particular number means.

If you do use numbers then the simplest solution is to add a lookup table that converts them to the appropriate descriptions so that you can substitute those for the numbers.

[r937]

I have been persuaded ...

and whatever you do, don't let whoever this was try to get you to use ENUM

[TomTees]

It would be more of a pain using numbers since then you'd need to be constantly looking up what a particular number means.

If you do use numbers then the simplest solution is to add a lookup table that converts them to the appropriate descriptions so that you can substitute those for the numbers.

I agree, but back in my MS Access days you'd be labeled a heretic for saying that! (Way too much over-normalization and using too many id's and codes in that world?!)

Thanks,


TomTees

[TomTees]

and whatever you do, don't let whoever this was try to get you to use ENUM

Yah, I think we had that conversation earlier. (Or I did with someone?!)



TomTees