Using column names as row ids for another table

Darth_Cow

Here's the situation: I have a table named hits with a date column (PHP style, just an integer), and columns for a number of different sites, identified by a siteid which keys to the information for a site in the table sites. The table sites has the columns siteid and name. I'd like to be able to query the table hits and use some sort of AS statement to rename the column names to sites.name where sites.siteid equals the column name. Is this possible? If so, could someone please give me an example? Thanks a lot!

EDIT: Just in case it's relevant, I'm running MySQL.

raymie

I do not think that it is possible in one sql statement.

The easiest solution would be to firstly extract the sites table into an array so that you can look it up by the siteid each time you retrieve a row from the hits table.

It would perhaps be better to normalise your database structure in the first place.

Darth_Cow

Originally posted by raymie
It would perhaps be better to normalise your database structure in the first place.

In that case, what would you recommend? It occured to me how I could do this via multiple queries, but I'd prefer not to. How would I "normalise" the database that I'm building? 😃

raymie

Did a quick search and there are lots of sites which will explain it for you. one of the first that came up was

http://www.guides.sk/reldb_dsgn/rdbd_n1.htm

http://www.guides.sk/reldb_dsgn/rdbd_nt1.htm

http://www.school-resources.co.uk/Normalisation.htm

This only goes up to 3rd normal form, but in general that is good enough for most sites, the higher levels such as Boyce-Codd, 4th and 5th normal forms are not as productive in general.

Darth_Cow

Actually, I ended up finding a more useful tutorial here, but thanks for your help. I forgot about how useful it could be to store multiple items under one id in one table.

To take an example of how I should have done things (that I realized from the site I mentioned):

-----------------------
| EMPLOYEE | LANGUAGE |
|----------|----------|
| Smith    | French   |
| Smith    | German   |
| Smith    | Greek    |
-----------------------