Which is better and faster?

[johnrb87]

Hi all

 

I wonder if you could give me some advice.

 

I basically have a mysql database of staff who work at a college, I have a front which displays details of a staff member, I grab the details using

 

userdetails.php?id=44 (44 being the ID of the staff member)

 

One field I have in my MySQL database is "title", which is basically

 

Mr

Ms

Mrs

Miss

Dr

 

At the moment, I store the "title" value in the database as "CHAR", so it appears as "Mrs" for example when I print

 

print $row['title'];

 

What I want to know is, which is the best method of storing and getting this data.

 

Is it best to

 

-----------------------------------------------------

 

1: Continue as I am and keep text values in the database stored as CHAR

 

-----------------------------------------------------

 

2: Change it so that instead of storing "Mr", "Dr" as CHAR, I would store them as numeric values, so;

 

2 would be Mr

3 would be Mrs

4 would be Dr

 

then do a

 

if($row['title'] == 2) { print "Mr"; } elseif($row['title'] == 3) { print "Mrs"; } elseif($row['title'] == 4) { print "Dr"; }

 

-----------------------------------------------------

 

3: Change it so that instead of storing "Mr", "Dr" as CHAR, I would store them as numeric values, but then have another database table which holds what value each number is, so my new database table would be like

 

--- title_values ----

 

id  value

2    Mr

3    Mrs

4    Dr

 

-------------------------

 

then on the front end, do a SELECT such as

 

SELECT `value` FROM `title_values` WHERE `id` = "'.$row['id'].'"

 

I guess the advantage to this one, is that I can modify the list of titles and expand it.

 

-----------------------------------------------------

 

which of those is generally considered better? faster? and less server intensive?

 

or is there a better way to do it?

 

Thanks very much

[taquitosensei]

The last one is what I would use. I try to separate everything into separate tables with ids. Within reason. That way it's easy to keep everything organized and normalized.

[MatthewJ]

I think the latter would be the best way to go...

 

But, unless you see a need to add or edit titles quite often, you're probably not going to see a lot of benefit from changing what you have now aside from having a clearly defined list of titles to use for say a drop down. That will obviously help for consistency of data.

 

That said though, there are not a lot of titles and if it just a db of staff, it probably is not all that big of a deal as the database can efficiently handle many more records.

 

I'm sure others will have opinions as well

 

Matt

 

 

[Canman2005]

A

[Canman2005]

A SELECT

[Canman2005]

Sorry, I would say a SELECT query to grab the title, it's what I would use

[johnrb87]

I think once this is modified with new changes, the client and 3rd party company might be taking it and doing a big rollout on existing systems which use their software, which I think is 250, and also offer it as an upgrade option when new customers buy there system.

 

So although this is used for around 20 staff at the moment, it could be used for customers, of which I have no clue on the volumes of customers some systems may have, some clients may even share a database, so there could be 150 systems running off the same database each with a few thousand customers per system for example.

 

So if I have to get the code to run a SELECT QUERY each time a user goes onto the page, surely that will clog up the database and server?

 

I guess it would be ok for 10 users logging in, but if tens of thousands of users logged in, would this still be the best way to do it?

 

I also have to pull the title on around 20 pages on the system, is it best to do a SELECT on each page, or store it as a variable when they login?

 

It could end up being something which is run on a regular basis as more and more people login to the system

 

[MatthewJ]

If you need to look details up anyway, adding a join to add the title to the same set of data is still one single database call. Asking it to grab another field is not a big deal.

 

For instance, instead of doing a single lookup for the title, you would probably do something like

 

SELECT tbl1.firstname, tbl1.lastname, tbl1.address, tbl1.phone, tbl1.titleid, tbl2.title
FROM tbl1
JOIN tbl2 ON tbl1.ttitleid = tbl2.titleid

 

Which would give you all of your data in a single query instead of doing a separate one for title alone.

 

hth

[ignace]

I don't see the benefit of using a separate table to hold the titles. At the most you could add a column that shows the full version and maybe even add a column for the description. But in all honesty I doubt you need this kind of NF. How you should design your database depends on how you are going to use it, a database in 5th NF that should serve a few million users a day is not really the right solution and will be quite costly instead 1st or 2nd should suffice.