Jump to content

table with multiple columns or several tables with redundant data?


sid0972

Recommended Posts

the question is a little different from the title though, as i provide the constraints.

 

If i insert a particular value in say, 8 tables, just once, and access other fields of that table many times, is it faster than having a big table with like, 20 columns, and accessing multiple fields, provided i would be using queries like " select * from table" and other time consuming queries like count of all entries in the table??

Link to comment
Share on other sites

1. You shouldn't be using select *, specify just the columns you need. Retrieving redundant data is inefficient.

 

2. Count all entries in a table is not time consuming as no records have to be read, mysql knows how many. (ie SELECT COUNT(*) FROM tablename).

 

Apart from those observations I haven't clue what you mean.

Link to comment
Share on other sites

I'll explain what am i doing.

I have a website, on php and mysql.

 

1st approach

 

A person registers, his username is stored in 8 different tables, which contain different info about him.

Referencing data becomes easier, but is very time consuming ( but, only once).

 

2nd approach

 

A person registers, his username is stored only once, but that table has many columns, say about 20.

Table becomes large.

 

which one is better?

 

I can select only the required values as you pointed out, but i would have to use "select * from table" at some places.

Edited by sid0972
Link to comment
Share on other sites

i have read about normalization , and, in a nutshell, it means that using the best possible queries to to the required job without having retrieve irrelevant data, right??

 

Wrong!.

 

It's about not storing redundant data, organizing your data and designing your tables so you can use the best possible queries to do the required job.

 

What is the proposed structure of your 8 tables and the alternative single 20-column table?

Edited by Barand
Link to comment
Share on other sites

its like this,

 

table 1 table 2

username username

info 1 info 3

info 2 info 4

 

and another 6 tables

 

main table

username

info 5

.

.

.

info 15

 

 

the fields info1-info 4 will be stored repeatedly for a single user, whereas in main table they are stored only once for a single user.

Link to comment
Share on other sites

When all I have seen is Info1 .... InfoN as data items there is no way I could give a verdict either way with any degree of certainty. As I've said, normalize your data and all will be well.

 

http://forums.phpfreaks.com/topic/273634-best-way-to-set-up-tables-when-multiple-values/?do=findComment&comment=1408360

Edited by Barand
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.