What way should i create my download counter database? MySQL PHP

Question

I would like to create a download counter for my files. Here are my current table fields:

download_counter table
_______________________
ID - int(6) - AutoInc,
Product - varchar(128),
Version - varchar(128),
Date - date,
IP's - LONGTEXT

I am wondering if I should use my current table or should I use a more dynamic and flexible table, like this:

ID - int(11) - AutoInc,
Product - varchar(128),
Version - varchar(128),
Date - date,
IP - varchar(45)

I'm not quite sure what the question is, I would definitely use a varchar instead of long text for an IP though — Doug Molineux, Jan 26 '11 at 17:30
oh and it is BAD form to call a 'Date' field, 'Date', always use non system / reserved names, and something more descriptive, like `dld_date` or `requestDate` — Jakub, Jan 26 '11 at 17:37

Michael Irigoyen · Answer 1 · 2011-01-26T17:43:53.127

4

I'd store a record per hit, but make sure you place indexes on any and all columns you'll be using to run SELECT queries on later.

Side note: If you want to store IP address in MySQL, you should take a look at the INET_NTOA and INET_ATON functions. They convert dotted notation IP address to 32 bit integers, allowing you to store the address in 4 bytes rather than 15 bytes. They must also be unsigned integers.

edited Jan 26 '11 at 17:43

answered Jan 26 '11 at 17:30

Michael Irigoyen

22,513
17
89
131

Until your site starts getting IPv6 traffic, in which case your IP's going to be every so slightly larger at 128bits instead of 32. Too bad MySQL doesn't have a native 128bit numeric type. – Marc B Jan 26 '11 at 17:37
@Marc B Good point. It would be nice if MySQL had a native type (similiar to PostgreSQL's `inet`). I've read some stuff about using blobs or BIGINTs with IPv6. There's some discussion on the topic here: http://stackoverflow.com/questions/420680/how-to-store-ipv6-compatible-address-in-a-relational-database/420688#420688 – Michael Irigoyen Jan 26 '11 at 17:40
Yeah, two unsigned bigints is the only practical way to go if you want to use a native numeric type. At least the hex form of a v6 address isn't too long, so you're not stuck with char(128) if it was textified binary. – Marc B Jan 26 '11 at 17:43

score 4 · Accepted Answer · answered Jan 26 '11 at 17:35

I would actually normalize your table properly:

download_counter {
   did,
   prodid,
   dldstamp,
   ip
}

and follow that up with your product table:

product_table {
   prodid,
   version,
   description,
   url,
   lastmodified,
   etc;
}

And you would just do inserts on your download_counter upon download request, referencing the prodid, from your product_table.

Simple enough and easy to normalize. You would then manage your products/downloads in the separate table.

What way should i create my download counter database? MySQL PHP

2 Answers2