Important: Read Before You Post

Go Back   AlMaghrib Forums > AlMaghrib Partners > Khutbah.com > Development

Reply
 
Thread Tools
Old 04-24-2006, 12:47 PM   #1
Abu Hurayrah
Guest
 
Posts: n/a
KMS - Current Schema

The following is a description of the current database schema for the KMS. Some examples of the data that it contains will be included, to demonstrate how the various data references itself.

If anything is not clear or needs further explanation, by all means, please do point that out.
  Reply With Quote
Old 04-24-2006, 10:09 PM   #2
Abu Hurayrah
Guest
 
Posts: n/a
Post Re: KMS - Current Schema

  • Interface
    • Holds definitions for terms used throughout the KMS interface
    • Columns
      • Id
        • int(10) unsigned NOT NULL auto_increment PRIMARY KEY
      • Label
        • varchar(255) NOT NULL default ''
      • LanguageId
        • int(10) unsigned NOT NULL default '0'
      • Name
        • text NOT NULL default ''
  • Items
    • Primary table that defines all types & content
    • Columns
      • Id
        • int(10) unsigned NOT NULL auto_increment PRIMARY KEY
      • Type
        • int(10) unsigned NOT NULL default '0'
      • DateAdded
        • varchar(255) NULL default NULL
      • DateProduced
        • varchar(255) NULL default NULL
      • Author
        • int(10) unsigned NULL default NULL
      • ThreadId
        • int(10) unsigned NULL default NULL
      • EmanRushProductId
        • int(10) unsigned NULL default NULL
      • MediaURL
        • varchar(255) NULL default NULL
      • Active
        • tinyint(1) unsigned NOT NULL default '1'
  • ItemsNodes
    • Relates Items to one-another, forming relationships based on context
    • Columns
      • ParentId
        • int(11) NOT NULL default '0'
      • ChildId
        • int(11) NOT NULL default '0'
      • PRIMARY KEY (ParentId,ChildId)
  • Language
    • Holds the actual content of each Item & relates Items in different languages
    • Columns
      • ItemId
        • int(10) unsigned NOT NULL default '0'
      • LanguageId
        • int(10) unsigned NOT NULL default '0'
      • Name
        • varchar(255) NULL default NULL
      • Content
        • mediumtext NULL default NULL
      • Footnotes
        • text NULL default NULL
      • PRIMARY KEY (ItemId,LanguageId)
  Reply With Quote
Old 04-24-2006, 10:32 PM   #3
Abu Hurayrah
Guest
 
Posts: n/a
Re: KMS - Current Schema

The schema, unfortunately, is rather complex due to some design decisions by the designer of the database (*cough cough*me*cough cough*). In retrospect, the design is actually very cumbersome, though at the time, the primary issue to solve was not developing an elegant database schema, but rather, how to create a "typeless" system. This was achieved by merging the concept of "Types" of content and the content itself. This ended up creating more problems than it solved.

I will post another thread that will contain some much-needed improvements to the database, which will include (but are not limited to) the following:
  • Create a more relationally-based model (e.g., separate tables for each type: Author, Khutbah, Article, Type, etc.) This includes creating & using appropriate foreign-key relationships as well.
  • Establishing some consistency guidelines, e.g.
    • Every table will have a unique, primary key field ('Id')
    • Consistent type usage
      • Positive integers: int(10) unsigned NOT NULL default '0'
      • Short strings: varchar(255) NOT NULL default ''
      • Long strings
        • Interface values & descriptions: text NOT NULL default ''
        • Article & Khutbah bodies: mediumtext NOT NULL default ''
      • "toggle" fields (e.g., Active): enum('0','1') NOT NULL default '0'
  • Removal of all NULL values from table rows
  • Conversion of tables to properly support multiple languages (i.e., convert to using UTF-8 internally)
  Reply With Quote
Old 04-25-2006, 11:48 AM   #4
ashiq
Qabeelat Majd
 
Join Date: May 2005
Location: Banu Maysara
Posts: 1,637
Re: KMS - Current Schema

Is your database in 3NF akhi? Do you know what 3NF is? (Minimal to no redundancy based on certain mathematical principles.)

I took DB recently, so if you send me the whole schema, I can dust off my memories inshaAllah and see if I can validate it for you ... though I'm sure there are tools out there that probably do that already.
ashiq is offline   Reply With Quote
Old 04-25-2006, 11:51 AM   #5
Abu Hurayrah
Guest
 
Posts: n/a
Re: KMS - Current Schema

Quote:
Originally Posted by ashiq
Is your database in 3NF akhi? Do you know what 3NF is? (Minimal to no redundancy based on certain mathematical principles.)

I took DB recently, so if you send me the whole schema, I can dust off my memories inshaAllah and see if I can validate it for you ... though I'm sure there are tools out there that probably do that already.
Just by looking at it, it should be clear that it is hardly normalized at all. The biggest give-away is the Items table. If you saw the data itself, you would find a lot NULLs in the column fields.

I would like to bring the schema up to at least 3NF, but the goal it not so much to reach a particular level of normalization as it is to at least bring it to some level of sanity. That goal is hinted it in my notes above.
  Reply With Quote
Old 04-25-2006, 12:31 PM   #6
ashiq
Qabeelat Majd
 
Join Date: May 2005
Location: Banu Maysara
Posts: 1,637
Re: KMS - Current Schema

Khayr then. I suggest you use your DB knowledge to the max--eliminate weak entities and use 3NF and so on--because of ihsaan. Wallahu 'alim, that's just my opinion...

If you need a hand with the DB, email me inshaAllah.
ashiq is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Current Memorization Threads: Chopped up Recitation ashiq Qur'an Memorization 6 11-22-2005 09:05 AM
CURRENT NUMBER of ASAD REGISTRANTS! Tooba I Qabeelat Asad 4 11-16-2005 07:32 PM


All times are GMT -4. The time now is 05:48 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2015, vBulletin Solutions, Inc.
All content copyright © 2005 AlMaghrib Institute. All rights reserved. No part of this site may be copied without written permission from the administration. The views, posts, and opinions expressed by members of the forum are not necessarily those of the staff and management of AlMaghrib or the Institute itself.