Setting up a Complex Database - Just looking for approval on structure

[eluser]jedd[/eluser]
As kgill observes, you are some way off 3NF. A good hint is when you see numbers creeping into your column names - you know you're going down the wrong path.

Something else that may be helpful - generate your table descriptions using SQL. This means you can later run a simple script to (re-)build your database, plus it makes it easier when showing people and asking their opinion. is_loop might make perfect sense to you, but makes none to me (for example).

Here's my CREATE_file.mysql script, to demonstrate

Code:

# file
#
# Apart from AVATARs, every 'attachment' (image, doc, etc) is managed via
# this table, which is pretty much just a UUID->image identifier lookup.
#
# Files are stored in a sub-directory that's named after the first 4 bytes of
# their given PUID, in an attempt to improve directory scans / lookups.
#
# Of course, this is handled entirely within PHP, to allow for easy changes
# to that algorithm.  The database doesn't track such trivialities.

CREATE TABLE file  (
    id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    puid           CHAR(36) NOT NULL,                 # uuid generated on file upload -- @TODO change this to do BINARY-16 UID's later - see pdb_helper
    extension      CHAR(4) NOT NULL,                  # original filename extension (tolower()'d on the way in) - eg. 'jpeg', 'pdf', 'ppm')
    type           CHAR(1) NOT NULL,                  # enums aren't that useful here, so instead (i)mage, (d)oc, etc
    member_id      INT UNSIGNED NOT NULL,             # who let the file in - FK(member.id)
    uploaded       DATETIME NOT NULL,                 # when they let the file in
    filename       CHAR(255) NOT NULL,                # the original filename as provided by the user - kept for people who may download the file
    description    TEXT(255) NOT NULL,                # provided by the member that sends us the file
    confirmed      BOOL NOT NULL DEFAULT FALSE,       # moderator has confirmed OK for public consumption
    PRIMARY KEY (id)
    );

I would suggest some consistency is handy, too.

Stick with plural or singular table names - my preference is singular - but just pick one and run with it.

Consistently have an id (unsigned, int, auto-inc) field as your primary key. There are occasions where you may not want to do this, but if you're asking whether the above design is okay .. you're probably better off sticking with the id approach.