sid0972 Posted January 21, 2013 Share Posted January 21, 2013 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?? Quote Link to comment https://forums.phpfreaks.com/topic/273445-table-with-multiple-columns-or-several-tables-with-redundant-data/ Share on other sites More sharing options...
Barand Posted January 21, 2013 Share Posted January 21, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/273445-table-with-multiple-columns-or-several-tables-with-redundant-data/#findComment-1407375 Share on other sites More sharing options...
sid0972 Posted January 22, 2013 Author Share Posted January 22, 2013 (edited) 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 January 22, 2013 by sid0972 Quote Link to comment https://forums.phpfreaks.com/topic/273445-table-with-multiple-columns-or-several-tables-with-redundant-data/#findComment-1407437 Share on other sites More sharing options...
Barand Posted January 22, 2013 Share Posted January 22, 2013 20 is not large Quote Link to comment https://forums.phpfreaks.com/topic/273445-table-with-multiple-columns-or-several-tables-with-redundant-data/#findComment-1407441 Share on other sites More sharing options...
sid0972 Posted January 22, 2013 Author Share Posted January 22, 2013 thank you Quote Link to comment https://forums.phpfreaks.com/topic/273445-table-with-multiple-columns-or-several-tables-with-redundant-data/#findComment-1407493 Share on other sites More sharing options...
Barand Posted January 22, 2013 Share Posted January 22, 2013 On the other hand, ensure your data is correctly normalized Quote Link to comment https://forums.phpfreaks.com/topic/273445-table-with-multiple-columns-or-several-tables-with-redundant-data/#findComment-1407504 Share on other sites More sharing options...
sid0972 Posted January 24, 2013 Author Share Posted January 24, 2013 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?? not talking about 3NF or 5NF or BCNF, they are way over my head. Quote Link to comment https://forums.phpfreaks.com/topic/273445-table-with-multiple-columns-or-several-tables-with-redundant-data/#findComment-1408003 Share on other sites More sharing options...
Barand Posted January 25, 2013 Share Posted January 25, 2013 (edited) 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 January 25, 2013 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/273445-table-with-multiple-columns-or-several-tables-with-redundant-data/#findComment-1408079 Share on other sites More sharing options...
sid0972 Posted January 25, 2013 Author Share Posted January 25, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/273445-table-with-multiple-columns-or-several-tables-with-redundant-data/#findComment-1408218 Share on other sites More sharing options...
Barand Posted January 25, 2013 Share Posted January 25, 2013 The repeated data should certainly be in separate tables so avoid the all-in-one table solution. Quote Link to comment https://forums.phpfreaks.com/topic/273445-table-with-multiple-columns-or-several-tables-with-redundant-data/#findComment-1408250 Share on other sites More sharing options...
sid0972 Posted January 26, 2013 Author Share Posted January 26, 2013 so what am i doing is not the worst approach?? Quote Link to comment https://forums.phpfreaks.com/topic/273445-table-with-multiple-columns-or-several-tables-with-redundant-data/#findComment-1408397 Share on other sites More sharing options...
Barand Posted January 26, 2013 Share Posted January 26, 2013 (edited) 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 January 26, 2013 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/273445-table-with-multiple-columns-or-several-tables-with-redundant-data/#findComment-1408412 Share on other sites More sharing options...
sid0972 Posted January 26, 2013 Author Share Posted January 26, 2013 thanks again Quote Link to comment https://forums.phpfreaks.com/topic/273445-table-with-multiple-columns-or-several-tables-with-redundant-data/#findComment-1408419 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.