1 table vs more tables with less fields

Databases
1

I am planning to create an user table. Data will be: Username, e-mail, password, image, age, … favourite items, friends… and about 30 more fields.

I am wondering what is a better choice:
Create 1 table with all those fields or more tables with less fields?

Let’ take for example friends field. I can put all friends id-s into 1 field as text (friend1:friend2:friend3:friend4) and than use PHP explode function to display all friends or I can create extra table for friends. So is it better to use …SELECT friends FROM user… or SELECT * FROM friends WHERE userid=3?

I am assuming that the first case will be better for performance because it needs to pull only 1 row. Where is the edge between 1 table vs more tables?

Thank you

2

not necessarily

it will require a table scan to find all rows which have a certain value inside a field that contains a comma-delimited list of values

whereas properly normalized, finding a certain value can utilize an index and therefore will have ~way~ better performance

3

Avoid storing multiple items in the same row. You should follow proper normalization practice and use a proper many to many relationship schema using a separate table to relate people to their friends. So in this case more tables with less fields would be your answer.

4

But where is the limit what to use in the same table? I will give you all possible things, please tell me how many of them would be better to put it in extra tables to get an idea:

-user id
-username
-first name
-second name
-address
-country
-language
-age
-gender
-professional level
-job position
-company
-about me
-hobbies
-countries I visited
-My updates (like twitter)
-my photos
-my videos
-status
-favourites
-profile views
-date of last login
-date of registered
-people visited my profile

Thanks a lot!

5

anything which is plural is suspicious

6

Can a user have more than one address?
Can a user speak more than one language?
Can a user have more than a single job?
Can a user visit more than one country?
Can a user have more than a single twitter update?
Can a user have more than a single hobby?
Can a user have more than a single photo?
Can a user have more than a single video?
Can a user have more than one favorite?
Can a user have more than a single person view their profile?

The answer to all those questions is most likely yes. So it would be smart to use separate tables with foreign keys to the user in which each item belongs.

7

agreeing with previous posts, try not to build tables to suit already known queries. You will have more in the future. store them as per rules of NORMALISATION and look up FOREIGN KEYS and INDEXING and CONSTRAINTS and FOREIGN KEYS

to refer to your previous post, everything between ‘hobbies’ and ‘profile_views’ (inclusive), as well as ‘people_visted_my_profile’ should be in a separate table like this (I think).

create table hobbies
( hobby_id int not null auto_increment
, user_id int not null
, hobby varchar(99)
, CONSTRAINT hobby_user_fk
foreign key (user_id)
references users(user_id) on delete cascade
) engine=innodb…etc.

assuming the main table with user_id is called ‘users’

other tables for all those cols, as mentioned, should be of similar format. make sure data type of the parent key is the same as foreign key eg INT or VARCHAR(99) etc.

oh yeh, read up one ‘one-to-one’ relationships and ‘one-to-many’ relationships so you know which type of table to create ~ n-m or m-m.

hth

bazz

8

bazz, what is the purpose of the hobby_id auto_increment?

:wink:

9

As usual there were errors in my post. :frowning: Thanks for spotting one :cool:


create table hobbies
( hobby varchar(99) not null
, user_id int not null
, CONSTRAINT hobby_user_fk
foreign key (user_id)
references users(user_id) on delete cascade
, primary key (hobby,user_id)
, reverse_ix (user_id,hobby)
) engine=innodb......etc.

Primary key and revere_ix to ensure suitably efficient indexing whatever the query. (I think).

bazz

10

yup, the existence of reverse index means that every query has a covering index

most many-to-many relationships are like this – only two columns, and together they form the primary key

this is a really interesting case, because the hobby column is not an actual “foreign key” to a hobbies table (most relationship tables have two columns where both are foreign keys)

the hobby column is a sort of conceptual or virtual foreign key, but i’m not sure that the application here requires the potential hobbies to be pre-registered in another table, so having it as a “data” column is fine