| Author |
 Topic  |
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
 Posted - 14 December 2004 : 15:56:23
|
Since I am no wiz at designing database tables, maybe I could get some suggestions from you guys.
I would like to keep track of the number of times mods (or just files in general) are downloaded from my site. Along with how many times it has been downloaded I also would like the folowing info stored: the date each time a mod was downloaded, the mod name, the location of the mod.
I have setup 2 tables already that keeps track of how many times the mod has been downloaded.
Table: MOD_INFO
Fields:
ID (auto_increment, integer, primary key) Hold unique value
FILE_ID (tinyint) Hold id of the file to download
DOWNLOADS (integer) Holds how many times it has been downloaded
DOWNLOAD_DATE (varchar) Supposed to hold the date the mod was downloaded. But i think this is setup wrong.
Table: MOD_NAME
Fields:
FILE_ID (primary key) Hold id of the file in MOD_INFO
FILE_TITLE (varchar) Holds the name of the mod
FILE_PATH (varchar) Holds the location of the mod
The MOD_INFO table is setup wrong of course, since I only have it updating the date the mod was last downloaded, which not what I want.
I am creating an asp page which will give me a display of how many times a mod has been downloaded per day or per month. And I will be able to edit the mod title and location from the page too.
So if someone can suggest a design for the mod_info table, I'll be grateful. 
I know it's probably simple but I'm no good at database design. |
Support Snitz Forums
|
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 14 December 2004 : 16:04:55
|
I'm guessing you'd be inserting a new entry every time somebody downloads something... if thats the case I'd set it up somethin like below
M_DOWNLOADS (table)
-M_ID (autonumber)
-FILE_ID (number crossreferenced to file id)
-M_DATE (date of download)
-M_IP (ip of user who d/l'd)(optional)
-M_BROWSER(browser user used to d/l)(optional)
|
-Stim |
 |
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 14 December 2004 : 16:55:41
|
| Yes, I will be updating or inserting into the database whenever someone downloads a file. So you suggesting creating a new table called M_DOWNLOADS that holds the date and the file id of the file that was downloaded? |
Support Snitz Forums
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 14 December 2004 : 17:34:19
|
Do you want to know how many times a mod was downloaded or how many times a file related to the mod was downloaded? Your naming strategy strikes me as a bit odd ... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 14 December 2004 : 18:04:08
|
I want to know how many times a mod has been downloaded on a given day and month. So i am guessing I would have to insert a new record each time a mod is downloaded, with the file id and the date. Then do a COUNT() or something like that.
Yeah, I know. I was trying to come up with a name that was short and made sense, and that was what i came up with it. |
Support Snitz Forums
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 14 December 2004 : 18:35:20
|
Ok, would you like to distinguish between mod versions?
Also, a mod is commonly composed of different files. In your own model I suppose file name is the name of the zipped file containing all file mods, right?
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 14 December 2004 : 18:52:54
|
Was thinking of each being distinguished by thier FILE ID. I assign each file a unique number.
I wasn't planning on including mod versions, but sure, we can distinguish them.
The files are zipped files, containing individual files in the zip, yes. |
Support Snitz Forums
|
Edited by - Davio on 14 December 2004 18:54:48 |
|
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 14 December 2004 : 19:44:51
|
I would say keep all information regarding versions of the mods, and compatibility in whatever table the FILE_ID references, and use the new table strictly for your data you want to keep.
I maintain my database outline above, of course if it was me, I'm into keeping stats, so I'd probably make fields for the browser, OS, IP the user who downloaded the file was using. Along with the date.
And if you have external sites accessing your downloads, you could put a referer field in there, to see where the user that downloaded your file came from. |
-Stim |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 14 December 2004 : 20:15:27
|
Ok, here it goes then:
I would suggest three tables:
1. Mod Info Table: (MOD)
MOD_ID - AutoNumber and Primary key
MOD_NAME
MOD_DESCRIPTION
2. Mod version info table (MOD_VERSION)
VERSION_ID - AutoNumber and Primary key
MOD_ID - Foreign key to MOD_INFO table, relates versions to mods.
MOD_VERSION_NUMBER - Mod version number
MOD_VERSION_ COMPATIBILITY - Description of Snitz versions compatible with the mod version
FILE_ID - Same as yours
FILE_TITLE - Same as yours
FILE_PATH - Same as yours
2. Mod Downloads Info (MOD_DOWNLOAD table)
DOWNLOAD_ID - Autonumber and Primary Key
VERSION_ID - Foreign key to downloaded version
DOWNLOAD_DATE
(... and then any other info you'd like to keep, like Member_ID, etc).
For proper normalization, you could have a FILE_INFO table, where you'd place FILE_ID and FILE_TITLE and FILE_PATH and keep a single FILE_ID foreign key column in the MOD_VERSION table. However, if you have a different FILE_ID for each Mod version, there is really no need for that. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 14 December 2004 20:15:56 |
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 15 December 2004 : 01:20:08
|
Stim, I won't rule out the option to expand my little project to include more info on each download, but since I only have a few files and not a lot of downloads, it might not warrant all that extra info.
Wow Rui. Trying to wrap my head around your suggestion. So those 3 tables are to be added along with my existing tables?
The version id seems to be playing a major part in the tables. I'm not only offering mod files but files in general which doesn't really have or need a version number.
I appreciate the suggestions greatly. Who knew table design could be so confusing? uhg! |
Support Snitz Forums
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 15 December 2004 : 05:07:44
|
quote:
Originally posted by Davio
Wow Rui. Trying to wrap my head around your suggestion. So those 3 tables are to be added along with my existing tables?
Yes, they are, if you like the design.
quote:
The version id seems to be playing a major part in the tables.
Version id is meant to identify the mod version and, as a given file is uniquely identified with a mod version, it also identifies the downloaded file.
quote:
I'm not only offering mod files but files in general which doesn't really have or need a version number.
Now you tell me  ...
You only talked about mods, nothing about files being the most important concept... Ok, I guess that would require a different structure, indeed. Will post a new proposal in a while...
Ah these pesky customers. This is gonna cost ya good when I send you the bill . |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 15 December 2004 : 05:26:41
|
Ok, here it goes another suggestion, that distinguishes between non-mod downloads and mod downlods:
The main concept is now the file, which can be associated with either a mod or something else
1. FILE_INFO table:
FILE_ID - Same as yours
FILE_TITLE - Same as yours
FILE_PATH - Same as yours
GOODIE_TYPE - Indicates whether the file is a mod (value 0) or something else (value 1, for example)
For a mod, there still is mod version info:
2. Mod version info table (MOD_VERSION)
VERSION_ID - AutoNumber and Primary key
MOD_ID - Foreign key to MOD_INFO table, relates versions to mods.
MOD_VERSION_NUMBER - Mod version number
MOD_VERSION_ COMPATIBILITY - Description of Snitz versions compatible with the mod version
FILE_ID - Foreign key to file table
The mod info table remains unchanged
3. Mod Info Table: (MOD)
MOD_ID - AutoNumber and Primary key
MOD_NAME
MOD_DESCRIPTION
To specify the info for non-mod downloads, I would use a OTHER_GOODIE table:
4. OTHER_GOODIE
FILE_ID - Foreign key to file table and primary key to this table as well
GOODIE_DESCRIPTION - The description associated with the download
Finally the info on the actual downloads, which uses FILE_ID as the foreign key, now, instead of version id:
5. Mod Downloads Info (MOD_DOWNLOAD table)
DOWNLOAD_ID - Autonumber and Primary Key
FILE_ID - Foreign key to downloaded file
DOWNLOAD_DATE
(... and then any other info you'd like to keep, like Member_ID, etc).
This would be it. It's a bit more complex, but distinguishes nicely between downloaded mods and other downloadable goodies.
I hope this is clear. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 15 December 2004 05:27:22 |
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 15 December 2004 : 05:42:44
|
quote:
Ah these pesky customers. This is gonna cost ya good when I send you the bill
Just can't take those pesky customers either Rui. They get on my last nerve. lol
I appreciate it Rui. I'll make it up to you in kind.
Thanks. Will start implementing that right away. |
Support Snitz Forums
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
| |
Topic |
|
Topic Locked
.