sql - Mysql: is it better to split tables if possible? -

to make understand question i'll give example: have chat web app many rooms, let's 5 rooms. people can choose stay in 1 room , choose @ login.

when choose room have retrieve people in room, can structure db in 2 ways: each room 1 table people being records; rooms in 1 table, people records , column indicating room in;

in first case query be:

select * 'room_2' 1 

in second case query be:

select * 'rooms' room = 'room_2' 

which best? think parameter consider performance, right?

in example, no, because people 'like' objects , should therefore in same table. people , rooms in 1 table primary key on people, in simple example. table rooms(pk_person, personname, table_id)

but want talk structure want consider website grows. you’ll want 3 tables, 1 each object (chat rooms, people) , 1 relationships.

chat_rooms(pk_chatid, chatname, maxoccupants, other unique attributes of chat room) people(pk_personid, firstname, lastname, other unique attributes of person) room_people_join(pk_joinid, fk_chatid, fk_personid, enterdatetime, exitdatetime) 

this “highly normalized” structure. each table collection of objects, join allows many many relationships, , object rows not duplicated. so, person attributes (name, gender, age) never duplicated in person table. also, person table never defines chat rooms person in, because person in one, many, none, or may have entered , exit multiple times. same concept applies chat room. chat rooms features, such background color, max occupants, etc. have nothing people.

the room_people_join important one. has unique primary key chat rooms person in , when there. table grows indefinitely, tracks usage. including relationship table logically normalizes database.

so how know users in chat room 1? join people , rooms join table respective primary , foreign keys in clause, ask columns want in select clause, , filter chat room 1 , people haven’t yet left.

select p.firstname, p.lastname, r.chatname room_people_join j     join people p on j.fk_personid  = p.pk_personid join chat_rooms r on j.fk_chatid = r.pk_chatid r.exitdatetime not null     , pk_chatid = 1 

sorry that’s long winded, extrapolated question database growth.