Member IDS or username

[jaymc]

In a bit of a pickle, I designed my database quite some years ago when I had little knowledge of normalisation etc

 

My database has grew enourmously so any changes will effect quite a lot including the php etc

 

If it has to be done, fair does, but just some questions first

 

 

At the moment, I have 30 tables. lets use my messages table as an example

 

the message table consists of

 

FROM = Username of sender

TO = Username of member

 

My quesstion is, rather than using usernames in the table as a unique indentify , should I use member IDs

 

Obviously the benifit of using a member ID is efficient, faster searching and small indexs and total table size

 

However, if i am to do this, for every quere where I need to pull in the username, I will now have to add an INNER JOIN to search by member ID the members table and add there actual username

 

This of course has its over head, although more practical

 

At current, I can get away with no joins for quite a lot of queries as I already have both usernames in that table, converting them to the members ID's means JOINS galore

 

What do you guys suggest?

 

Will joins slow me down, or will converting to member ID's reduce table and index size thus speed things up in theory

[fenway]

Obviously, a JOIN is "expensive" in the way that you describe it... but denormalization is worse.  Especially because you didn't denormalize it on purpose.

 

It's not joins "galore", it's one for each... and with an index, it should be almost instantaneous.

[jaymc]

You would suggest creating a global relationship between the tables using the member id providing a join is used in every occurance to map the username

 

I suppose the real question is, how much of a negative impact does it have with a chunky database with words rather than numbers to avoid the join...

 

Hmm

[fenway]

Numbers are also much faster to join than words.