Hi(gh)!

As announced, here my first version of a relational database structure 
for images on p.b.i:

DATABASE SYSTEM: MySQL (preferably higher than 4.1, allowing subqueries)

TABLES:

images - with the following fields:
- ID (primary key, auto_increment)
- Filename (including path)
- Title
- POVerNick_ID <- table 'povernicks', n:1 relation
- Date (published on p.b.i)
- Format_ID <- table 'formats', n:1 relation
- Size (in bytes)
- Width
- Height
(Number of pixels and aspect ratio can be calculated within MySQL 
queries, therefore no explicit field is needed)
- POVVersion <- table 'povversions', n:1 relation
- ImageVersion
- PostProcessed
- (NumColors)
- (AvgBrightness)
- (AvgSaturation)
- (FreqHue0-35)
- (FreqHue36-71)
- (FreqHue72-107) ... (FreqHue324-359)
- (FreqBright0-10)
- (FreqBright11-20) ... (FreqBright91-100)
- (FreqSatur0-10)
- (FreqSatur11-20) .. (FreqSatur91-100)

(the entries for fields given in brackets are to be calculated by an 
external utility program)

povernicks (nicknames of POVers)
- ID (primary key, auto_increment)
- Nickname

poverreal (real names of POVers - if known!)
- ID (primary key, auto_increment)
- FirstName
- Surname

povernicks_poverreal (linking table between tables 'povernicks' and 
'poverreal', m:n relation)
- ID (primary key, auto_increment)
- POVerNicks_ID <- table 'povernicks'
- POVerReal_ID <- table 'poverreal'

formats
- ID (primary key, auto_increment)
- Extension
- Name

povversions
- ID (primary key, auto_increment)
- Name

keycont1st (content keywords, 1st hierarchy level)
- ID (primary key, auto_increment)
- Name

keycont2nd (content keywords, 2nd hierarchy level)
- ID (primary key, auto_increment)
- Name
- Keycont1st_ID <- table 'keycont1st', n:1 relation

keycont3rd (content keywords, 3rd hierarchy level)
- ID (primary key, auto_increment)
- Name
- Keycont2nd_ID <- table 'keycont2nd', n:1 relation

keycont (content keywords, base level)
- ID (primary key, auto_increment)
- Name
- Keycont3rd_ID <- table 'keycont3rd, n:1 relation

images_keycont (linking table between tables 'images' and 'keycont', m:n 
relation)
- ID (primary key, auto_increment)
- Images_ID <- table 'images'
- Keycont_ID <- table 'keycont'

keypov (POV features keywords)
- ID (primary key, auto_increment)
- Name

images_keypov (linking table between tables 'images' and 'keypov', m:n 
relation)
- ID (primary key, auto_increment)
- Images_ID <- table 'images'
- KeyPOV_ID <- table 'keypov'

scripts (scene files, include files)
- ID (primary key, auto_increment)
- Filename (including path)
- Type (main script vs. include)
- Size (in bytes)
- NumLines

images_scripts (linking table between tables 'images' and 'scripts', m:n 
relation)
- ID (primary key, auto_increment)
- Images_ID <- table 'images'
- Scripts_ID <- table 'scripts'

externalfiles (any non-script file used for the calculation of the 
scene, such as textures, heightfields, bumpmaps...)
- ID (primary key, auto_increment)
- Filename (including path)
- Type
- Format_ID <- table 'formats', n:1 relation
- Size (in bytes)

images_externalfiles (linking table between tables 'images' and 
'externalfiles', m:n relation)
- ID (primary key, auto_increment)
- Images_ID <- table 'images'
- Externalfiles_ID <- table 'externalfiles'

addsoftware (additionally used software, i. e. modellers, 
post-processing tools, script generators etc.)
- ID (primary key, auto increment)
- Name
- Link (to download, if freeware/OpenSource)

images_addsoftware (linking table between tables 'images' and 
'addsoftware', m:n relation)
- ID (primary key, auto_increment)
- Images_ID <- table 'images'
- AddSoftware_ID <- table 'addsoftware'

See you in Khyberspace!

Yadgar

Post a reply to this message