• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Setting up a Complex Database - Just looking for approval on structure

#1
[eluser]Gwarrior[/eluser]
I've read many guides on database normalization through my years as a web developer, but I am now on a fairly large project, and wondering if my structure is correct.

It is a Content Management System btw:

CONTENT DB

content table
id
page
trigger
is_new
post_date
group_name
is_loop
description


fields table
field_id
main_id
title
field_1
field_2
field_3
field_4
field_5
field_6
field_7
field_8
image_1
image_2
image_3
image_4
image_5
timestamp
author


groups table
group_id
group_name


Where 'main_id' in fields is the 'id' of the content table, and groups is simply there to create the dropdown where you can select which existing group you want to use when creating trigger.

Obviously there are some other features put in there like the 'is_loop' and 'is_new' which are both boolean values, but is the general structure right?

BTW, I'm using the MyISAM engine, but can use the InnoDB if you feel it would be appropriate.

Thanks in advance and I apologize if I didn't include enough pertinent information to answer the question.

#2
[eluser]kgill[/eluser]
You've read many guides on normalization and you have to ask if that structure is correct? The very first rule of normalization is get rid of repeating groups: image_1, image_2, image_3, field_1, field_2, field_3...

What happens a year down the line when someone says I want more than 8 fields or 5 images? Repeating data belongs in it's own table so that you don't have to alter the table to solve that problem.

#3
[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.

#4
[eluser]rogierb[/eluser]
If this is for a contentmanagement system, you might want to add multilanguage support, extended publication (when, what time, specific roles, end date/time, when modified etc.) and ordering.

If there's going to be a lot of content, then splitting in groups might no be enough. Maybe some form of categories per group? or groups per category?

As for the content itself, 3 fields will normaly do: Title, intro and content. I've used this for FAQ. news, articles and so on. If there is need for more fields, just add another table like Jedd and Kgill suggested.
I call mine "content_detail" where everything besides content and publication data is stored. Files, fields, dates, whatever I like and need.

#5
[eluser]Gwarrior[/eluser]
Thanks for all the replies.

I couldn't possibly describe how I'm designing this CMS without either writing a book or mangling the legitimacy of the whole thing. By and large, my goal is to create a FULLY customizable CMS where users can upload fieldsets (by uploading fieldset_1.php (or any other #) to a uploaded_views file, which is then pulled out when the users selects that fieldset and tries to add/update data. Essentially, they create the HTML fields and labels, so we don't have to use 'Title' for things other than the Title, which seems to be the way people 'hack' CMS's. This is too confusing for my clients, as I have learned.

The only other CMS I know that does something similar, is Perch (grabaperch.com).

After looking at the database that Wordpress builds upon installation, I think you guys are completely right that I should go down a new path for this one.

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?

#6
[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?

#7
[eluser]Gwarrior[/eluser]
Thank you for your time, Brian.

As I was writing out a reply to say I don't think your solution would work, I reread your idea and it seems very valid and like it would work just fine. Ha, happens all the time for me.

So thanks for what seems like the answer. At least for now. Wink

#8
[eluser]Monotoba[/eluser]
A little late I know however, you may want to take a look at the EAV database scheme. It allows you to dynamically define fields and field values. I think PHP Arch. had an article on EVA a few years ago. Or just google Entity Attribute Value Database scheme. It can result in some complex queries however using an ORM such as DataMapper or DMZ can ease the sheme development.

Hope this helps


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.