Ok say i have 1 table for Users it stores USER_NAME, USER_ID, AND USER_COLOR and im making a new table for Logs that will store USER_NAME
when viewing a page and i need to recieve this data i can do 2 querys one for logs and then search users where USER_NAME=USER_NAME
or i can store USER_NAME, USER_ID, AND USER_COLOR all in logs then i can get all the data with just 1 query.
what would be faster or better?
sample query
$sql = "SELECT id, username, level, namecolor FROM users ORDER BY level DESC";
$query = mysql_query($sql) or die(mysql_error());
$count = mysql_num_rows($query);
while($row = mysql_fetch_object($query)) {
$Member_id = htmlspecialchars($row->id);
As you are using MySql, your database is a relational one.
This gives you (as you already know) many advantages, so, instead of replicating the user information (already stored in its own table) in other table (logs in this case), you'd better to do joins when needing more information about a user.
Let the users information to be in its own table, so, when querying logs data you may do this:
SELECT logs.log_id, logs.log_date, logs.user_name, users.user_color
FROM logs
LEFT JOIN users ON logs.user_name = users.user_name
Another point: think what would happen when replicating user info, if you add a new field to the user table (email for instance).
Better would be the one in third normal form. In simplistic terms, that means every non-key column should be dependent on the key, the whole key, and nothing but the key, so help me Codd :-)
There are sometimes good reasons to revert to lesser normalisation levels but they're rare, and you need to both understand and mitigate the potential problems, such as ending up with multiple user names per user ID or vice versa. I would say in this case stick to the multi-table version.
It is better to join these tables:
select users.*, logs.* from logs inner join users on logs.USER_NAME=users.USER_NAME
You will get all information in one query.
I think you can have it in different tables as core User information like USER_NAME, USER_ID, USER_COLOR is going to be only one for each user. On the other hand logs can be many for each users. So if you store everything in log table your database will use more space for storing additional information like USER_NAME, USER_ID, USER_COLOR every time some logs happens for a user.
I think you should read about JOIN in mysql to see how you can combine both tables in one query and get the result. More readings on JOIN Link
I can't propose a query here because it depends on what kind of data and how you want it out of the database.
It would be better to keep both of the tables.
Database design and the first normal form dictate that you should not have the same data multiple times.
So if you save all the data in the second table you are making a bad design practice.
There is a good example http://en.wikipedia.org/wiki/First_normal_form