I am piecing together a picture website. The fundamental schema's really quite simple MySQL, but I am getting some trouble attempting to represent possible admin flags connected by having an image ("inappropriate", "copyrighted", etc.). My current notion is the following:

tblImages (
    imageID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ...
);

tblImageFlags (
    imageFlagID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    imageID INT UNSIGNED NOT NULL,
    flagTypeID INT UNSIGNED NOT NULL,
    resolutionTypeID INT UNSIGNED NOT NULL,
    ...
);

luResolutionTypes (
    resolutionTypeID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    resolutionType VARCHAR(63) NOT NULL,
    ...
);

(cut down for easy reading through assorted foreign secrets and indexes have been in order, I swear)

tblImageFlags.flagTypeID is foreign-keyed on the research table of flag types, and understandably tblImageFlags.resolutionTypeID should be foreign-keyed on luResolutionTypes.resolutionTypeID. The problem at hands is the fact that, whenever a flag is first released, there's no logical resolution type (I'd declare mtss is a good utilization of NULL) however, if your value is placed, it ought to be foreign-keyed towards the research table.

I can not look for a MySQL syntax workaround for this situation. Will it exist? The very best final contestants are:

  • Add an "unmoderated" resolution type
  • Give a NULL admission to luResolutionTypes.resolutionTypeID (would this work within an AUTO_INCREMENT column?)

Just insight!

PS Bonuses to whoever informs me whether, within the situation of databases, it's "indexes" or "indices".


Follow-up: because of Bill K for mentioning what switched to be considered a syntax error within the table structure (don't set a column to NOT NULL if you would like it to permit NULL!). And when I've enough karma to provide you with individuals bonuses, I'll :)

You are able to solve this by permitting NULL within the foreign key column tblImageFlags.resolutionTypeID.

The plural of index ought to be indexes.

Based on "Modern American Usage" by Bryan A. Garner:

For regular reasons, indexes is the more suitable plural, not indices. ... Indices, though less pretentious than fora or dogmata, is pretentious nonetheless. Some authors prefer indices in technical contexts, as with mathematics and also the sciences. Though not the very best plural for index, indices is allowable meaning "indications." ... Steer clear of the singular indice, a back-formation in the plural indices.