SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Database Design

Hybrid View

  1. #1
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Design

    I'm having trouble thinking of a good design for this database I need to make.

    The thing that makes it complicated is that there will be X types of content, each type requiring different parameters, but all types being equal with regards to the other tables.

    The only solutions I can think of would be to make a separate table for each content type. To get the information onto the page I would have to get the table name from a related table and then run another query to get the actual content.

    I don't like that solution.

    I could also make a single table with a bunch of unspecific fields that could be used for different things depending on which type is being used. I would then have to differentiate within the code what to use what for.

    That solution seems better to me, the only thing I don't like about it is that it's making code do things that I wish the database was doing, and that the form fields wouldn't be specific to what kind of data they'd need to hold. They'd basically all have to be varchars no matter what.

    This is in MySQL by the way.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well if the different 'types' have similar content then I think it wouldn't be a bad idea to keep them in one table. If they are wildly different (and have different numbers of columns) then I'd create some tables like this:
    Code:
    CREATE TABLE content_type (
      content_typeid INTEGER AUTO_INCREMENT PRIMARY KEY,
      content_type   VARCHAR( XX ) NOT NULL UNIQUE );
    
    CREATE TABLE content (
      contentid      INTEGER AUTO_INCREMENT PRIMARY KEY,
      content_typeid INTEGER NOT NULL REFERENCES content_type( content_typeid ),
      content_title  VARCHAR( XX ) NOT NULL UNIQUE );
    
    CREATE TABLE some_type (
      contentid  INTEGER PRIMARY KEY REFERENCES content( contentid ),
      fields_for_this_table... );
    
    CREATE TABLE some_type2 (
      contentid  INTEGER PRIMARY KEY REFERENCES content( contentid ),
      .... );
    That way you still have a table with all of the names in it but the data is stored in tables which make sense for the data.

    You didn't provide enough information though -- if you can give an example of two different 'types' and their data then I think we all could give a bit more help.

  3. #3
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd like to stay away from the multiple table set up.

    This is for a template driven CMS. A user will be able to select a different type of content to display on a page, with 5 content types allowed per page.

    The content types would be like this:

    1. Block of Text
    2. Block of Text with image (right or left aligned)
    3. Table
    4. List
    5. List opposite image

    And so on, basically allowing them to choose how their page appears without them having to know any HTML.

    So the "block of text" would only need 2 form fields, an identifier and the text. The text with image would need 4, an identifier, text, image name, alignment.

    For "table" content I'm going to have to use a separate table, no way around that, or atleast an extra table to hold each row.

    Right now I'm thinking the best way will just be to use one table, and let there be blank fields for some rows. Then in the code I can make a function that processes and displays the db info for each content type, and I'll simply pass the info to the appropriate function based on whats listed in the parent table.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  4. #4
    There is no general chat z0s0's Avatar
    Join Date
    Aug 1998
    Location
    Melbourne
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Probably not a lot of use to you, but in PostgresSQL I achieved this with:

    Code:
    CREATE TABLE	contenttypes
    (
    	type	CHAR(16) NOT NULL,
    	PRIMARY KEY(type)
    );
    
    INSERT INTO contenttypes VALUES ('article');
    INSERT INTO contenttypes VALUES ('resource');
    
    ...etc
    
    CREATE SEQUENCE contentid START 1;
    
    CREATE TABLE	content
    (
    	id		INTEGER	DEFAULT	CURRVAL('contentid'),
    	CONSTRAINT 	content_id_pk PRIMARY KEY(id),
    	type		CHAR(16) NOT NULL,
    	CONSTRAINT	type_fk FOREIGN KEY(type) REFERENCES contenttypes(type) ON UPDATE CASCADE,
    
    ......
    
    );
    
    CREATE TABLE	article
    (
    	id		INTEGER	DEFAULT	NEXTVAL('contentid'),
    	CONSTRAINT 	art_id_pk PRIMARY KEY(id),
    	CONSTRAINT 	art_id_fk FOREIGN KEY(id) REFERENCES content(id) ON DELETE CASCADE,	
    	title		VARCHAR(255),
    	blurb		TEXT,
    	keywords	TEXT,
    	authorid	INT,
    	CONSTRAINT 	art_auth_fk FOREIGN KEY(authorid) REFERENCES author(id),
    	body		TEXT
    );
    
    CREATE TABLE	resource
    (
    	id		INTEGER	DEFAULT	NEXTVAL('contentid'),
    	CONSTRAINT 	res_id_pk PRIMARY KEY(id),
    	CONSTRAINT 	res_id_fk FOREIGN KEY(id) REFERENCES content(id) ON DELETE CASCADE,	
    	title		VARCHAR(255),
    	description	TEXT,
    	url		VARCHAR(255),
    	imageid		INT,
    	CONSTRAINT 	res_img_id_fk FOREIGN KEY(imageid) REFERENCES image(id),
    	popup		BOOLEAN,
    	width		INT,
    	height		INT
    );
    .... etc.

    In this manner, each content type is essentially a subset of the "content" table. You can use some of the more obscure functions (eg COALESCE()) to retrieve lists from all content types in one query.

    But this all academic since MySQL can't do any of this ;-)
    Wormly Server Performance Monitoring
    Don't wait for an SMS at 4am. Find out what's really
    going on and fix the problem. www.wormly.com/website-monitoring

  5. #5
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ya I didn't mention I was using MySQL, but that was a correct assumption.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums


Bookmarks

Posting Permissions

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