Which is better.
Let's say I want to track clicks each day. Which is better, to have one Mysql entry that has a LONGTEXT and all 365 days of click data in it.
Or 12 database entries with TEXT that has 31 elements in it.
Or 365 database entries that have int(1) for each day.
I am thinking that method 2, having 12 entries with an imploded("|",..) array of the click data is the best.
I don't realy have any experience to draw upon, so I am only thinking this is best for effiecent * database size.
Comments?
Greg K posted this at 11:48 — 4th December 2004.
He has: 2,145 posts
Joined: Nov 2003
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
camperjohn64 posted this at 00:36 — 5th December 2004.
They have: 10 posts
Joined: Nov 2004
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?
JM
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 posted this at 02:29 — 5th December 2004.
He has: 2,145 posts
Joined: Nov 2003
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 posted this at 14:46 — 5th December 2004.
They have: 10 posts
Joined: Nov 2004
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?
JM
thisnew posted this at 13:26 — 17th March 2005.
They have: 8 posts
Joined: Feb 2005
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
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.