thisnew — Thu, 17 Mar 2005 13:26:28 +0000

camperjohn64 wrote:

Is this bad MySQL programming? Am I going about it all wrong?

JM

Well, kind of, yeah. It's not so much a matter of familiarity with MySQL, as with relational database design. It's not the same as object oriented design.

In general, arrays of this kind are to be avoided in relational db design, you want to be storing your sequences in consecutive of rows, not within the same cell.

So, short answer is that more fine-grained is better. Store each day in a different row, or for more info, even each click in a different row.

For a mathematical take check: http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter7/node1.html

Or perhaps best of all, I'd recommend this book: http://www.amazon.com/exec/obidos/tg/detail/-/0201752840/qid=1111065929/sr=8-2/ref=pd_csp_2/002-9582967-9672015?v=glance&s=books&n=507846

camperjohn64 — Sun, 05 Dec 2004 14:46:01 +0000

Hmm, I think my C++ experience is mixing with my lack-of-mysql experience.

In C++, here is an example of what I want to do. How about a daily histogram of when he clicks just for example's sake:

class users
{
     int    user_id;
     int    daily_histogram[7];
};

void users::user_arrived(int unix_time_seconds)
{
     int whichday = unix_time_seconds / 86400; // Turn seconds into days
     daily_histogram[whichday % 7] ++;
}

Now in C++ this would bump up the day that the user visited. Here is how I am thinking it is supposed to be done in MySQL:

Table: users

Name	 | Type		| Description
===========================================================
UserID   | UNSIGNED INT | (AUTONUMBER) Unique ID for each User 
DailyClicks | int(7)  | The user's clicks seperated into day.

Again, maybe I am trying to shoehorn MySQL into a C++ way-of-thinking.

Is this bad MySQL programming? Am I going about it all wrong?

Greg K — Sun, 05 Dec 2004 02:29:44 +0000

Ok, assuming that mySQL did allow for an array of ints, show me the table layout you were planning, and from there I should be able to guide you to a good way to do it.

Off what you are saying right now, different users, different web pages, I would say:

Table: data

Name	  | Type	 | Description
===========================================================
HitDate   | DATE	 | The date recorded.
HitUserID | UNSIGNED INT | User ID from the Users Table
HitPageID | UNSIGNED INT | Page ID from Pages Table
HitCount  | UNSIGNED INT | The actual count for the user/page/date

'
Table: users

Name	 | Type		| Description
===========================================================
UserID   | UNSIGNED INT | (AUTONUMBER) Unique ID for each User 
UserDesc | VARCHAR(30)  | The user's real name, or e-mail, IP, etc.

'
Table: pages

Name	| Type		| Description
===========================================================
PageID  | UNSIGNED INT | (AUTONUMBER) Unique ID for each Page URL
PageURL | VARCHAR(60)  | The URL of the page being tracked

This way, the users are only listed once, as well as the pages. As far as space, it is true that my method would take up a little more, however really about all it is duplicating is the date value, which to me is very little extra for the extra ease in accessing ranges of hits.

-Greg

camperjohn64 — Sun, 05 Dec 2004 00:36:40 +0000

Well I want to use int, but from what I can find on the internet, MySQL does not support arrays of ints.

This is exactly what I am asking. How do I have an array of ints? I can't find any documentation on it or any examples.

username char (32) default ''

This is an array of chars (string). I can set a name like "john", with j,o,h,n

username_foobar int (32) default ''

Theortically, this should be an array of ints. But for the life of me I can't figure out how I can put 10,20,30,40... into this. From everything I've read, MySQL doesn't seem to support this. It seems the only useful form of int is INT(1)...

Show me? If I say please?

ps - when you mean a seperate record for each day, do you mean a seperate entry into the database for each day? That could get huge no? If you have a seperate entry for each day, for each user, for each tracker, for each webpage...?

Greg K — Sat, 04 Dec 2004 11:48:56 +0000

For trackign clicks per day, I would use a separate record for each day, with two fields, DATE and HITS. This method gives you the best option for getting ranges.

Also consider, text takes more room to store. and then you have have the overhead of programming to break things up and such, waht if you want to calculate a total for a month? a week? 2 months?

-Greg