Which is more effiecient?

[Howardison]

Hello,

 

I have this one question, I dont really know where to look for the answer. Hopefully this will help me.

 

 

I have this mysql database, and i wanted to make a change to it. I have come up with 2 solutions, but i do not know which is better, what i mean by that is server and time efficiency.

 

 

Solution one:

 

Create a new table and add the current # of members to the new table. Each member will be identified by an Unique ID, therefore there will be one variable UNIQUEID, and there will be 900 rows, since there are 900 members in the whole database.

 

But there are 32 other variables i want to add. Each variable represents a medal (medal_id), and each of them will have two other variables that goes along with it. awarded_by, and awarded_on

 

So, In conclusion - There will be 1 UNIQUEID, 32 MEDAL IDS, 2 extra variables for each (64 variables).. and the total comes out to be 99 Variables in new table.

 

But If there are 99 variables * it by 900 members, that will be over 89,000 cells in the database.

 

$sql = @mysql_query(\"SELECT * FROM member_medals WHERE UNIQUEID = \"$UNIQUEID\"\");

$row = mysql_fetch_array($sql);

extract($row);

 

 

(Which takes less cpu time.)

 

Solution two.

 

Everything is same as above, except instead of 99 variables there will be only 4 variables. (UNIQUEID, medal_id, awarded_by, awarded_on)

 

But since each row can ONly represent one medal, then 32 rows are needed to represent one member.

 

But if im doin it this way, only the current active members will have a row to this table. At this current moment there are 240 members, and 1200+ medals given out, so only 1200+ rows.

That is 4,800 cells.

 

But if i do that way, the # of rows will increase alot faster than in solution one.

 

$sql= @mysql_query(\"SELECT * FROM member_medals WHERE UNIQUEID = \"$UNIQUEID\"\");

while($row = mysql_fetch_array($sql)) {

extract($row);

}

 

 

 

 

 

IN CONCLUSION- If let say i will use it for a long time and the databae will get bigger, in the long term what is more effecient?

[Barand]

I would recomend that use Google to search for \'tutorial database normalize\' for the best way to design your tables.

[pauper_i]

Barand is right, normalization will help a lot.

 

Basically you need to decide how the database will be used in order to decide the best layout for it. You have a variable number of parameters and need to decide which ones are important to your particular application. For example, does each member always have all 32 medals? Would it be better to have a field entry for each medal with an ID pointing to the awards table (no ID = no medal) and the award info is recorded in a separate table?

 

Then there\'s some deeper questions to cover:

Can a member lose a medal?

What happens to the medals if a member leaves?

Is the member status affected by a medal or lack of it?

 

Finally, you need to consider how you want to get the information out of the database:

List of all members by medals earned

List of all medals by members

List of members not having earned a particular medal

etc.

 

Once you have all this, you can then use the information to decide on your table structures. We can only postulate and hypothesise here, but unless we have all the information as to your intended useage of these tables, we can\'t begin to make suggestions.

 

Don\'t worry too much about the number of rows or columns in a particular table; that\'s SQLs problem. Your main concern is to get the structures organised in a way that will make your coding easier to create and maintain while providing the data requested in the most efficient manner.

 

There\'s a good article on this at http://www.phpbuilder.com/columns/barry20000731.php3 that you could try as a starting point.

 

D

 

**EDIT** forgot to mention - take a look at the sticky topic at the top of this forum, written by shivabharat - that will help you too! D