user table

[jimfog]

I am constructing a table where the registered users of the site will be listed.These though are divided into 2 categories:

• Regular Users
• Business Users

As such I am considering adding a column to the users table where it say if this specific user is business one or a regular. I am not sure though if this is the most optimal way to go.In other words I am creating a column which is value will be either "Business" or "Regular". And since the users will be many(either type) the above values will be appearing multiple times. Do you think there is a flaw in such a design,that I am storing redundant info? An maybe I should create a separate table?

[niche]

If you have a use for that info, yes. If you don't, no. If you think you probably will have a future use, yes.

Edited November 7, 2012 by niche

[justsomeguy]

If you use an enum column it will only use 1 byte or less per row to store that information instead of 1+ bytes per character if you use a text column.

[thescientist]

Going along JSG's suggestion, you could advocate make a user_type table to store all the types and map them to numeric values ("Regular" => 0, "Business" => 1, etc) so that way you can change the text (if you needed to) only in the user_type table without have to change the text for every user row.

[jimfog]

SO, I should go for 2 tables.I did not quite understood your argument though...about changing text. Are you suggesting with this scheme, that in the user table, in the user type column(in addition with the user type table) I will have only numeric values where these will point/connect to this user_type table?

[thescientist]

correct.

Are you suggesting with this scheme, that in the user table, in the user type column(in addition with the user type table) I will have only numeric values where these will point/connect to this user_type table?

it would be a foreign key from the user (type column) table to the user_type table. My only point is in the event you want to change it from say "Business" to "Customer" for example, you only have to change it in one place. Not every user record in the user table. Edited November 8, 2012 by thescientist

[jimfog]

Thanks...

[jimfog]

I assume that the enum_type column will be the user_type column in the user table. What about the the column in the user_type table where it will hold the value from the user_type column-the foreign key in other words. I do not know much about foreign keys.If we mark the the column in the user_type table-that will hold the value of the user_type column of the user table- as beingforeign, does that mean that this column will be of the same type as the column from where it gets the foreign key? And I assume it will be InnoDB tables

[jimfog]

I finally created the 2 tables, from the one(users) a foreign key goes to the other(usertype). This means the user_type column in the user table contains numeric values 0,1,2,3 etc. This, though, is bad if someone attempts to read the table-a human I mean.So, am I right to assume that a human readable database comes in contrast with a db that aims to be well organised-meaning that these are two conflicting goals.

[justsomeguy]

I've never designed a database with one of the goals being that a person should be able to open any arbitrary table and instantly understand everything. The database is for the computer. If the person wants readable data then they use a join query, that's what reports are for. That's why I store dates and times as Unix timestamps. If I want to know what date it represents I run it through a function, I keep them as integers because that's what the computer is good with. The point is to make it fast and efficient, I don't care if I export the data from a single table and any random person can't make complete sense out of everything.

[jimfog]

Ok....I got your point.

[jimfog]

Since the table structure is OK now I have to face another problem now.The logic is that the user fill in a forms where he says there if he is a regular user or a business user-depending on what he says in the form theuser_type column in the user table will get the corresponding value-as stated in a post above. The problem begins when we consider that the business users are divided into other categories, doctors, salons etc...and as such tables must be created foreach of these that will hold relevant data. As I see it, when a user declares being a doctor, we must enter a value in the user type column of the user table and add a record in thedoctor;s table-2 queries.What worries me, is that each time a user says he is a doctor or other business category, I must use If statements to check that and and inserta record in the corresponding table plus the original user table. If we have 7 business categories, that means around 7 if statements(if doctor then(insert in doctor's table) else if salon then(insert in salon's table)). I am trying to think if there is a more practical way to do this. what do you think? Am i clear, or do I have to clarify better, what I am trying to achieve here.

[justsomeguy]

If you can make them generic and group them all in the same table with different values that signify what they are, or null columns for things that don't apply, then go for that. If that's not an option then it sounds like you've designed a system that requires 7 if statements (not that there's anything inherently wrong with 7 if statements). For what it's worth, the registration code for my major application is 390 lines, and that doesn't even include any output or other include files. It reads or writes data in 12 database tables.

[jimfog]

For what it's worth, the registration code for my major application is 390 lines, and that doesn't even include any output or other include files. It reads or writes data in 12 database tables.

When you say registration code...I assume you mean the registration process, and it reads/writes 12 DB tables.I never though that things can go so complicated. It seems that coming up with a DB design is more of an art. regarding my issue now: So, you are suggesting I gather all the business users in one table... Edited November 14, 2012 by jimfog

[justsomeguy]

It's hard for me to make a decision like this without understanding all of your requirements. Go with what you feel is right, if it's not the best way then you'll find out soon enough and you'll have learned something.