User:Woozle/PostFerret/sql/post

About

• Purpose: content type -- has a title and content; optionally, an owner and summary
• Fields:
  • ID_Owner: ID of user who owns this post (NULL means ownership is not relevant)
    • Some post types will not have owners, but it's a common enough thing that I thought it should go in at a low level.
  • ID_Title: ID of atom for title for page headers and narrower listings
  • ID_Summary: ID of atom for one-line description for wide listings, intro paragraph for blog posts, etc.
  • ID_Body: ID of atom for main content
• History:
  • 2013-03-12 Created, but not yet in use.
  • 2013-03-15 Most fields moved from post to atom; added ID_Owner.
  • 2013-06-28
    • Decided to remove URI until there's a clear purpose and need for it.
    • Removed owner/summary uniqueness requirement; can't see need for this either (e.g. multiple posts might be called "status report", for example).
    • Added Body field to make this independent of the containing CMS.
  • 2013-07-21 ID_Owner needs to be DEFAULT NULL (not NOT NULL) if it's going to be optional. Duh.
  • 2015-06-29 Decided that editable text content should be in a separate table, to make it easier to track revisions

SQL

<mysql> CREATE TABLE `pf_post` (

  `ID_Atom`     INT              NOT NULL COMMENT "ID of atom this post is using",
  `ID_Owner`    INT          DEFAULT NULL COMMENT "ID of user who owns this post (optional)",
  `ID_Title`    INT              NOT NULL COMMENT "ID of atom for title",
  `ID_Summary`  INT          DEFAULT NULL COMMENT "ID of atom for summary (optional)",
  `ID_Body`     INT              NOT NULL COMMENT "ID of atom for body of post",
  PRIMARY KEY(`ID_Atom`)
)
ENGINE = MYISAM;

</mysql>

Obsolete

• URI: I haven't yet decided if this really belongs here or how to formally define it, but it could be used for either:
  • a page on the site which is set aside for more information about the given post
  • the post's official URI on the site

<mysql> `URI` VARCHAR(255) DEFAULT NULL COMMENT "optional site-relative URI for more information",</mysql>