www.webdeveloper.com
Results 1 to 5 of 5

Thread: [RESOLVED] [CMS Research] Fields on database - simple/single or huge

Hybrid View

  1. #1
    Join Date
    Apr 2012
    Posts
    10

    resolved [RESOLVED] [CMS Research] Fields on database - simple/single or huge

    Hi guys

    I'm researching info to create a CMS.

    About database storing fields ( object fields ) and values ( field values ), ie for the class and for the content.
    I've seen only 2 approaches here:
    • simple/single value table
    • multi value table


    Simple/single value table (ex: integer):
    Code:
    CREATE TABLE value_integer (
      id  INT,
      value INT,
      -- other needed columns for connecting with the content
    )
    Multi value table:
    Code:
    CREATE TABLE value (
      id  INT,
      bool1 TINYINT,
      bool2 TINYINT,
      bool3 TINYINT,
      integer1 INT,
      integer2 INT,
      integer3 INT,
      string1 VARCHAR( 2048 ),
      string2 VARCHAR( 2048 ),
      string3 VARCHAR( 2048 ),
      text TEXT,
      datetime DATETIME,
      -- other needed columns for connecting with the content
    )
    The second approach doesn't look the better since most of the columns will be empty.
    However for the fields that need several values will need that the query has several tables and rows, instead of 1 simple table/row.

    What are your opinions about this?

  2. #2
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,213
    Because some content systems are designed to allow the addition of content and allow a different date than that of the current date, eg you may have an article published on a site you want to copy and as a date reference you keep that date and the content date is the current date, you can run in to some problems with pushing dates out of sequence if your CMS has a limited date function.

    Alternatively you may not want content to appear until a certain date, therefore you would get items published before they were due if same date referencing is limited.


    So you would need a database that has something like:-

    TABLE AUTHORS
    AUTHORS.ID
    AUTHORS.EMAIL
    AUTHORS.NAME
    AUTHORS.DISPLAYNAME
    AUTHORS.SECURITY

    TABLE PUBLISH
    PUBLISH.ID --> the ID that would form part of the query string
    PUBLISH.CREATED --> loaded on to system
    PUBLISH.DATE --> publish on or published on this date
    PUBLISH.EXPIRE --> expire / don't show after a certain date
    PUBLISH.AUTHOR_ID --> the ID ref of the Author
    PUBLISH.CONTENT_ID --> the ID of the content
    PUBLISH.SOURCE_ID --> The Source Link information

    TABLE CONTENT
    CONTENT.ID --> the article ID
    CONTENT.TYPE --> the type of content, it could be a NEWS item or a PAGE
    CONTENT.KEYWORDS --> any tags or keywords associated with the article, useful for an internal search engine
    CONTENT.SUMMARY --> a summary of the article, a short version like an extended grabline
    CONTENT.BODY --> the article itself
    CONTENT.DELETED --> flag set if you want content marked as deleted, this could be a date reference and can also act as a flag

    TABLE SOURCE
    SOURCE.ID --> the ID
    SOURCE.URL --> where it is, eg http://somewhere.com/index.php?article=12345&...
    SOURCE.URL_DISPLAY --> what you want to display somewhere.com
    SOURCE.TITLE --> the title tag of the link what will display, eg "go to site blah blah"

    The scheme above would give you scope to have more than one author on your CMS system and also allow log in via that information

    Your various tables / groups form a lose type of normalized database where you have tables for authors, content and a simple table to bring those elements together, so you could incorporate a search feature for or pull all articles by a certain author that were containing certain keywords that were published on or between certain dates...
    Yes, I know I'm about as subtle as being hit by a bus..(\\.\ Aug08)
    Yep... I say it like I see it, even if it is like a baseball bat in the nutz... (\\.\ Aug08)
    I want to leave this world the same way I came into it, Screaming, Incontinent & No memory!
    I laughed that hard I burst my colostomy bag... (\\.\ May03)
    Life for some is like a car accident... Mine is like a motorway pile up...

    Problems with Vista? :: Getting Cryptic wid it. :: The 'C' word! :: Whois?

  3. #3
    Join Date
    Apr 2012
    Posts
    10
    First of all, thanks for the fast reply and sorry for my delay

    My idea for the CMS is to give the user the possibility to create the classes on the backend ( type of the objects, like landing pages, articles, or more specific stuff like portfolio, gallery, ... ), so the classes can't be hard coded on the DB

    And as far as I understood from that TABLE CONTENT this can't be achieved, or am I not understanding the idea?

    My inicial approach was:

    BASE

    TABLE OBJECTS -- Base object, it will serve for all objects
    OBJECTS.ID -- integer id
    OBJECTS.REMOTE_ID -- 32 char unique ID for inter systems communication
    OBJECTS.OWNER -- the original owner of the object
    OBJECTS.LASTEST_VERSION -- latest version of the object ( for example if it has first version publish and has a second version as a draft this will be 2 )
    OBJECTS.CREATED_AT -- timestamp of creation date/time
    OBJECTS.UPDATED_AT -- timestamp of last update date/time
    OBJECTS.DELETED_AT -- timestamp of the deletion date/time, can be null if it isn't trashed

    TABLE VERSIONS -- the version controller for the objects
    VERSIONS.ID -- integer id
    VERSIONS.OBJECT_ID -- OBJECT.ID key
    VERSIONS.CREATED_AT -- timestamp of creation date/time
    VERSIONS.CREATED_BY -- timestamp of last update date/time
    VERSIONS.STATUS -- atual status of this version archived, published or draft
    CONTENT CLASSES

    TABLE CONTENT_CLASSES -- object class
    CONTENT_CLASSES.ID -- integer id
    CONTENT_CLASSES.VERSION_ID -- VERSION.ID key
    CONTENT_CLASSES.IDENTIFIER -- a string identifier for usage on API
    CONTENT_CLASSES.URL_PATTERN -- the URL pattern
    CONTENT_CLASSES.OBJECT_PATTERN -- the displayable object name pattern
    CONTENT_CLASSES.CONTAINER -- container bool ( if the object can contain another objects or if it is a leaf )
    CONTENT_CLASSES.MAIN_LANGUAGE_ID -- LANGUAGE.ID of the base/original language

    TABLE CONTENT_CLASS_DETAILS -- the name and description of the class in the several languages
    CONTENT_CLASS_DETAILS.CONTENT_CLASS_ID -- CONTENT_CLASS.ID key
    CONTENT_CLASS_DETAILS.VERSION_ID -- VERSION.ID key
    CONTENT_CLASS_DETAILS.LANGUAGE_ID -- LANGUAGE.ID key
    CONTENT_CLASS_DETAILS.NAME -- name of class ( language specific )
    CONTENT_CLASS_DETAILS.DESCRIPTION -- description of the class ( language specific )

    TABLE FIELDTYPES -- field types for the class
    FIELDTYPES.ID -- integer id
    FIELDTYPES.CONTENT_CLASS_ID -- CONTENT_CLASS.ID key
    FIELDTYPES.IDENTIFIER -- string identifier for API
    FIELDTYPES.TYPE -- type of the field ( string, integer, selection, multioption, ... )
    FIELDTYPES.POSITION -- position on the class
    FIELDTYPES.DEFAULT_VALUE -- default value | null
    FIELDTYPE.REQUIRED -- if it is a required field
    FIELDTYPE.TRANSLATABLE -- if there is a translation
    FIELDTYPE.SEARCHABLE -- if the field is searcheable
    FIELDTYPE.INFO_COLLECTOR -- if the field is for information collector

    TABLE FIELDTYPE_DETAILS -- same as class, field also has the name and description in specific language
    FIELDTYPE_DETAILS.FIELDTYPE_ID -- FIELDTYPE.ID key
    FIELDTYPE_DETAILS.LANGUAGE_ID -- LANGUAGE.ID key
    FIELDTYPE_DETAILS.NAME -- name of class ( language specific )
    FIELDTYPE_DETAILS.DESCRIPTION -- description of the class ( language specific )

    TABLE FIELDTYPE_VALUES -- fieldtype values for the cases like dropdown lists where multiple values need to be specified
    FIELDTYPE_VALUES.ID -- integer id
    FIELDTYPE_VALUES.FIELDTYPE_ID -- FIELDTYPE.ID key
    FIELDTYPE_VALUES.LANGUAGE_ID -- LANGUAGE.ID key
    FIELDTYPE_VALUES.PARENT_ID -- FIELDTYPE_VALUES.ID key for the cases where the lists have parent topic
    FIELDTYPE_VALUES.POSITION -- position on the fieldtype ( in some cases this might be useful )
    FIELDTYPE_VALUES.KEY -- key value ( internal data, the displayed data is in the next table )

    TABLE FIELDTYPE_VALUE_DETAILS -- fieldtype values language details just like the others
    FIELDTYPE_VALUE_DETAILS.FIELDTYPE_VALUES_ID -- FIELDTYPE_VALUES.ID key
    FIELDTYPE_VALUE_DETAILS.LANGUAGE_ID -- LANGUAGE.ID key
    FIELDTYPE_VALUE_DETAILS.VALUE -- displayed value
    FIELDTYPE_VALUE_DETAILS.TIP -- tip on hovering
    FIELDTYPE_VALUE_DETAILS.DESCRIPTION -- longer description
    In some cases FIELDTYPE_VALUES wont be enough so there
    will need to do another table for that specific field type.

    CONTENT OBJECTS

    TABLE CONTENT_OBJECTS -- final object, like pages, images, formularies, ...
    CONTENT_OBJECTS.ID -- integer id
    CONTENT_OBJECTS.VERSION_ID -- VERSION.ID key
    CONTENT_OBJECTS.CONTENT_CLASS_ID -- CONTENT_CLASS.ID key
    CONTENT_OBJECTS.SECTION_ID -- SECTION.ID key ( sections like public/restricted/private, ... )

    TABLE FIELDVALUE_STRINGS -- string type of field to display on the final object
    FIELDVALUE_STRINGS.ID -- integer id
    FIELDVALUE_STRINGS.CONTENT_OBJECT_ID -- CONTENT_OBJECT.ID key
    FIELDVALUE_STRINGS.LANGUAGE_ID -- LANGUAGE.ID KEY
    FIELDVALUE_STRINGS.FIELDTYPE_ID -- FIELDTYPE.ID key
    FIELDVALUE_STRINGS.VALUE -- actual displayable value

    TABLE FIELDVALUE_INTEGERS -- integer type of field to display on the final object
    FIELDVALUE_INTEGERS.ID -- integer id
    FIELDVALUE_INTEGERS.CONTENT_OBJECT_ID -- CONTENT_OBJECT.ID key
    FIELDVALUE_INTEGERS.LANGUAGE_ID -- LANGUAGE.ID KEY
    FIELDVALUE_INTEGERS.FIELDTYPE_ID -- FIELDTYPE.ID key
    FIELDVALUE_INTEGERS.VALUE -- actual displayable value

    -- ... PLUS the remaining of possible values types ( as float, image, price, ... )
    OR instead of having the several type of fields have a single huge table as below?

    TABLE FIELDVALUES -- main value of fields to display on the final object
    FIELDVALUES.ID -- integer id
    FIELDVALUES.CONTENT_OBJECT_ID -- CONTENT_OBJECT.ID key
    FIELDVALUES.LANGUAGE_ID -- LANGUAGE.ID key
    FIELDVALUES.TYPE -- type of the value ( string, integer, text or float )
    FIELDVALUES.INTEGER -- this and next values are the values of the object ( only 1 is filled )
    FIELDVALUES.FLOAT
    FIELDVALUES.STRING
    FIELDVALUES.TEXT
    And I have the same doubt on the FIELDTYPES values, cause those would need to have the values repeated ( see eZPublish content class fields approach )

    ( about multi-language and versioning there is more thinking/research needed so that I don't duplicate too much info )

  4. #4
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,213
    The simplest design is that you have one table that holds all your content regardless of it ifs a galley picture, news article and so on, a field in that table could be used to indicate the content type.

    You have a table that stitches together all the data you have like an index / directory for the site.

    You can then pull information from the database depending on what you are looking for like articles by a specific author, by a type like news or select by date range and filter on other elements.

    All this type of requirement can be catered for by creating a query that you apply to your data set.

    Yes you get some designs that have a gallery table, content table, news table but that IMHO is a waste of time when you can normalize a data set so that you know where the data is, how to access it and you have a directory to help you put elements together.

    The authors table is just one example of a table that can have additional features applied to it like authority for log in to post an article, etc.

    What I posted was the basics of a very simple system, it is not meant to be the definitive answer to your needs but something that could help you formulate your own system.
    Yes, I know I'm about as subtle as being hit by a bus..(\\.\ Aug08)
    Yep... I say it like I see it, even if it is like a baseball bat in the nutz... (\\.\ Aug08)
    I want to leave this world the same way I came into it, Screaming, Incontinent & No memory!
    I laughed that hard I burst my colostomy bag... (\\.\ May03)
    Life for some is like a car accident... Mine is like a motorway pile up...

    Problems with Vista? :: Getting Cryptic wid it. :: The 'C' word! :: Whois?

  5. #5
    Join Date
    Apr 2012
    Posts
    10
    Thanks a lot

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles