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

[eluser]BrianDHall[/eluser]
[quote author="Gwarrior" date="1254806021"]The CMS I describe is currently FULLY operational on my server, having spent a couple of work days dedicated directly to it. I don't mind going back and reworking the entire thing, I just want to do it right this time. And what I fear is that the way you guys describe will make me have to remove the 'fieldset upload' portion, because how could a user specify his input fields without knowing that field_1, field_2 already exists, as in

Code:

<input type="text" name="field_1" />

<input type="text" name="field_2" />

If I can figure out how to do this, I could adopt a normal database structure. Any ideas?[/quote]

You might take a look at DMZ (datamapper overzealous extension) for some guidance on how to build database tables that permit relationships easily.

Here is my interpretation of how it should be:

content table:
id
page
trigger
is_new
post_date
group_name
is_loop
description

fields table:
id
main_id (if this should be related to the content table, call it content_id for clarity since MySQL doesn't natively support in-table foreign keys)
title
timestamp
author

field_options table:
id
field_id
field_number

images table:
id
content_id (or field_id, whatever you relate images to)
(any other useful image info would go here)

groups table:
id
group_name

Notes:

First, tablename_id is generally unnecessary. Most databases should have 'id' as a unique auto-incrementing integer primary key. This is minor, but it helps to avoid unnecessary repetition and allows you to use tablename_id as notation of an in-table foreign key when it is useful to do so.

As to your problem with the input items for fields, I should think you can handle this by checking the database for field_options.field_number - if it already exists, then you would just update the value, and if it doesn't then you'd make the new DB entry.

Does this sound like it would work, or is there something about this field_x/field_set upload thing I'm not getting?