Table Length Question...

Hi there,

I currently store some information about a users daily habits in a table.
The table has 4 fields per day, and another 4 fields as the keys. This
table, depending on the month, can be from (4 keys + (28 days * 4 fields per
day)) fields, to (4 keys + (31 days * 4 fields per day)) fields long...

The table layout is like such:
+-----+---+----+-----+------+------+------+------+------+--- ---+------------
--
|name |id |id2 |type |d01f1 |d01f2 |d01f3 |d01f4 |d02f1 |d02f2 |.....and so
on
+-----+---+----+-----+------+------+------+------+------+--- ---+------------
--

Performance wise, would it be better to have it laid out in a manner such as
+-----+---+----+-----+----+---+---+---+---+
|name |id |id2 |type |day |f1 |f2 |f3 |f4 |
+-----+---+----+-----+----+---+---+---+---+
So that each row, contains a single days details, rather than have a single
row, contain the entire months details?

Also, when i would do a select, if i wanted say d02f1, would it load the
entire row first, and then just give me that field?
-Select `d02f01` from `mytable` where [where clause]
Or would it jsut load that field...

Does these questions make sense? (they do in my head)

Steven Staples



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Table Length Question...

Not only should you definitely have one record per day, instead of one reco=
rd per month, you should think about normalizing your structure further. T=
ry these articles for tips on how to design a database structure:

http://dev.mysql.com/tech-resources/articles/intro-to-normal ization.html an=
d http://mysqldump.azundris.com/archives/20-Nermalisation.html and some her=
e http://www.keithjbrown.co.uk/vworks/mysql/

Good luck!

Regards,
Gavin Towey

-----Original Message-----
From: Steven Staples [mailto:sstaples@mnsi.net]
Sent: Tuesday, March 30, 2010 8:36 AM
To: mysql@lists.mysql.com
Subject: Table Length Question...

Hi there,

I currently store some information about a users daily habits in a table.
The table has 4 fields per day, and another 4 fields as the keys. This
table, depending on the month, can be from (4 keys + (28 days * 4 fields pe=
r
day)) fields, to (4 keys + (31 days * 4 fields per day)) fields long...

The table layout is like such:
+-----+---+----+-----+------+------+------+------+------+--- ---+-----------=
-
--
|name |id |id2 |type |d01f1 |d01f2 |d01f3 |d01f4 |d02f1 |d02f2 |.....and so
on
+-----+---+----+-----+------+------+------+------+------+--- ---+-----------=
-
--

Performance wise, would it be better to have it laid out in a manner such a=
s
+-----+---+----+-----+----+---+---+---+---+
|name |id |id2 |type |day |f1 |f2 |f3 |f4 |
+-----+---+----+-----+----+---+---+---+---+
So that each row, contains a single days details, rather than have a single
row, contain the entire months details?

Also, when i would do a select, if i wanted say d02f1, would it load the
entire row first, and then just give me that field?
-Select `d02f01` from `mytable` where [where clause]
Or would it jsut load that field...

Does these questions make sense? (they do in my head)

Steven Staples



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Table Length Question...

Your first table layout is horrible, the second one is only marginally
better. You should read up on database normalization.

I have no idea what id, id2 and type are but since they seem like they
are the same for every 'f' and every day, I am pretty sure they all
relate directly to the user so those should all go in the first table.
I will assume 'name' is the field you use to identify users. So your
second table, let's call it dftable, should have the following fields.

ID --- generic auto increment id for each record.
name -- foreign key to link this table back to your user table.
Fnumber -- this would be an int or maybe a set that would be set to
1,2,3, or 4
Date -- this would be the just like it says the date and you only have
one table not one for every month.
DFValue -- not sure what type of data this is.

you probably want a unique key on name, FNumber and Date. They could
also be your primary key instead of using ID, but I find it easier to
code my applications and deal with changes in the long run if you use a
single field auto increment value for your primary key.


a simple join with a group concat will give you back your first output
example.

However for you example where you want d02f01 the query would simply be
Note: since in my table I am using date, instead of just 02 we need the
whole date so I will assume it is Mar, 02 of 2010

SELECT u.`name`, u.`id`, u.`id2`, u.`type`, d.`DFValue`
FROM `user` u
JOIN `dftable` d USING(`name`)
WHERE `FNumber` = '1' AND `Date` = '2010-03-02'

If id, id2 and or type change over time and you need to keep track of
that you could add those fields in this table.

Chris W

Steven Staples wrote:
> Hi there,
>
> I currently store some information about a users daily habits in a table.
> The table has 4 fields per day, and another 4 fields as the keys. This
> table, depending on the month, can be from (4 keys + (28 days * 4 fields per
> day)) fields, to (4 keys + (31 days * 4 fields per day)) fields long...
>
> The table layout is like such:
> +-----+---+----+-----+------+------+------+------+------+--- ---+------------
> --
> |name |id |id2 |type |d01f1 |d01f2 |d01f3 |d01f4 |d02f1 |d02f2 |.....and so
> on
> +-----+---+----+-----+------+------+------+------+------+--- ---+------------
> --
>
> Performance wise, would it be better to have it laid out in a manner such as
> +-----+---+----+-----+----+---+---+---+---+
> |name |id |id2 |type |day |f1 |f2 |f3 |f4 |
> +-----+---+----+-----+----+---+---+---+---+
> So that each row, contains a single days details, rather than have a single
> row, contain the entire months details?
>
> Also, when i would do a select, if i wanted say d02f1, would it load the
> entire row first, and then just give me that field?
> -Select `d02f01` from `mytable` where [where clause]
> Or would it jsut load that field...
>
> Does these questions make sense? (they do in my head)
>
> Steven Staples
>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org