Content

1. Table design
2. Class design

Aim

Website functionality

• Add/edit person records
• Search one or more person
• See the genealogy chart for a family
• Find relationship between 2 person
• Search bride/groom
• Match bride/groom by horoscope
• Bride/groom places a wish list (a list of items they would like to receive as marriage gift)

Technology

• Database holds the data of people and maintains relationship between them.
• Java beans are used to pull the data from the db.
• Jsp is used to present the data and accept user queries.
• Servlets mediate beans and jsps
• xml, xsl (if required)

Design

Database table design

create table personTable (
	personId     mediumint not null auto_increment,
	name         varchar(40),
	initial      varchar(10),
	familiarName varchar(40),
	sex          enum ('m', 'f'),
	birthDate    date,
	expireDate   date,

	fatherId     mediumInt,
	motherId     mediumInt,

	religionId   mediumInt   fk classificationTable,
	sectorId     mediumInt   fk classificationTable,		 

	education    varchar(100),
	profession   varchar(100),

	street       varchar(40),
	placeId      mediumInt   fk placeTable,
	native       mediumInt   fk placeTable,

	nationality  mediumInt   fk placeTable,
 

	homePhone    varchar(20),
	workPhone    varchar(20),
	mobile       varchar(20),


	email        varchar(70),
	hideEmail    enum ('0', '1'),
	webpage      varchar(100),
	photo        varchar(100),

	bloodgroup   enum('A+', 'A-' ...),
      
	maritalStatus enum('single', 'married', 'divorce'),
	rasi          enum(0 - 12),
	nakshatram    enum(0-27),


	hobbies      varchar(200),
	description  varchar(500),

	recordStatus enum('available', 'pending'),
     
	primary key (personId)
);

// do we need a separate table or can we use varray in the personTable to hold list of spouse?
create table spouseTable (
	husbandId    mediumInt,
	wifeId       mediumInt,
	marriageDate date,

	primary key (husbandId, wifeId)
);

create table matrimonialTable (
	matriId     mediumInt,
	personId    mediumInt,
	
	height      int,
	weight      int,
	
	complexion  enum('fair', 'medium', 'black'),
	smoking     enum('never', 'sometimes', 'often'),
	drinking    enum('never', 'sometimes', 'often'),
	
	description varchar(500),
	expectation varchar(500),

	primary key matriId
);

create table HoroscopeMatchingFactor (
	girlRasi          enum(0-12),
	girlNakshatram    enum(0-27),
	boyRasi           enum(0-12),
	boyNakshatram     enum(0-27),
	matchingFactor    float,

	primary key   (girlRasi, girlNakshatram, boyRasi, boyNakshatram)
)

create table classificationTable (
	divisionId      mediumInt,
	baseDivisionId  mediumInt,
	description     varchar(100),
	divisionType    enum('belief', 'sector'),

	primary key divisionId
);

create table placeTable (
	placeId         mediumInt,
	basePlaceId     mediumInt,
	pincode         varchar(10),
	placeType       enum('country', 'state', 'city', 'town', 'village', 'area'),

	primary key placeId
);

Class design

class Person
{
	insertPersonRecord()
	{ 
	}
	
	updatePersonRecord()
	{
	}

	deletePersonRecord()
	{

	}

		
	retrievePersonRecord()   // from db
	{

	}

	retrievePersonName()
	{

	}

	formatPersonRecordForDisplay()
	{

	}

	formatPersonNameForDisplay()
	{

	}

	protected int personId;
	protected String name;

	protected Hashtable personData;  // all other info of a person

	protected Horoscope horoscope;
}



class Horoscope
{
	

	matchHoroscope(Horoscope otherHoroscope)
	{
		girlHoroscope = this;
		boyHoroscope = otherHoroscope;
		for(int i = 0; i < 10; i++)
		{
			call match[I](girlHoroscope, boyHoroscope);
		}

		return numberOfMatches, crispDesc, detailDesc;
	}

	// there r 10 match function.  
	// Each function returns a float indicating the matching 
	// criteria.  Also these function place their comment into  
	// crispDesc & detailDesc.
	match[0-9](Horoscope girlHoroscope, boyHoroscope)
	{

	}

	private static String rasiName[] = { "Mesham", � };
	private static String nakshtramName[] = {"Ashwini", �};

	private byte rasi;
	private byte nakshatram;

	private float numberOfMatches = 0;
	private String[] crispDesc, detailDesc;
}


class SessionManager
{
 	retrievePerson(int personId)
	{
		Person person = personPool.retrievePerson(personId);
	  	if (person == null)  // retrieve from db
	  	 	person = Person.retrievePerson(personId);
	  	return person;
	}
	  
	PersonPool personPool;
}


// a container to hold recently accessed person records in this session
class PersonPool
{
	retrievePerson(int personId)
	{
	}
	 
	addPerson(Person person)
	{
	  	// implement lru algorithm to handle person object
	}
}

class LogWriter
{
	

	int message(int messageLevel, int messageType, message)
	{
		/*
		messageLevel is an indicator useful to consider to enter this log or not.
		A global debugLevel is set with the number between 0 to 9

		If messageLevel falls below debugLevel, then the message is entered (to the db)

		messageLevel takes the following meaning :
		

    		
Error message
    		
Warnings
    		
Sql template
    		
Sql command
    		
Session state
		
		*/
	
	}

}

Web admin

Tasks for Web Admin

• Analyze the daily report generated by House keeping module and fix the issues
• Go thru ALL new/edited records and approve them

Web hosts chart

• Jsp 1.2
• Database (Oracle preferable; great with 9i)
  Since hosting with Oracle is beyond my budget, considering Postgresql
  MySql is not suitable; due to lack of subqueries, triggers, stored proc, transaction ...
• Unix preferable
• 75 to 100MB space
• Extras like xml parser, struts preferred

Criteria for a good Webhost

• Web space
  • File space to hold html, script, class, image files
  • Database space
  • Runtime memory (How to measure?)
  Are these size specification flexible?
• Deploy War file
• ftp, telnet facility
• scalability
• Popularity of the host
• Check the other sites in the host server
• If possible, get feedback from few site owners
• What is the load of the server? How many sites can it handle?
• Accounts
  • Number of ip addr (port) provided
  • Number of domains and sub domains
  • Number of schemas (users?) allowed in the database
  • Emails (pop, mime), forwarding, web access
• Backup, recovery procedure
• Security
• Software version
  • OS - Unix blend preferable
  • Jdk, jsp
  • Web server
  • Database
  • Other api
• Bandwidth
• Traffic volume
  • What is the upper limit?
  • What happens when the limit is crossed?
  • Do we have to pay more for the extra traffic?
  • How easy to upgrade to higher options?
• 24/7 hours reliability
• Auditing, statistical analyses of nature and behavior of users
• Technical support
  • Toll free number
  • Quick response for email
• Online help, user guide
• The big question. Cost
  • Setup cost (the site or individual component)
  • Any hidden cost?
• Can we get a trial period (1 month) for the web space?
  This will be useful to know the capabilities of the site before entering into a long term contract.

Development environment

• java 1.3
• Tomcat 4.1.12
  • JSP 1.2
  • Servlet 2.3
• Struts - MVC framework from apache.org
• Oracle 8 - Trying for a recent version (>= Oracle 8i)
• Have to download PostgreSql
• Ant 1.5.1 - a java based build tool from Apache
• Eclipse - a java IDE eclipse.org
• CoffeeCup - a html editor

Cost estimation

Issues

Hashing

• Ease of coding and maintenance
• Saves lots of space, because expecting most of the fields to be null. These null fields will not be in the hash.
• Modifying the table will affect only the xsl file (or any presentation layer)

• Accessing the attributes of the Person class will be 2 or 3 times slower than directly accessing a variable.
• Space for the field name stored along with actual value should be considered

Finding the relationship

• Can we implement this logic in plsql?
• Do we require java stored proc?
• Will these proc handle multi-thread?
• Do we have to use different temp table names?

Client side processing

• server has less work of formatting the presentation. The load is shifted to client which handles the xsl file.
• Once the xml doc (file?) is available to the client, that particular document (record) can be reused and displayed with different xsl files. In my application, I thought of designing a record pooling mechanism at session level, so that recently accessed records will be available immediately without the need to refer the db. But the same mechanism will be available if recent xml doc will be available at client machine and there is a mechanism to reuse them.

• Overhead cost of converting data into xml doc etc. Read in the book that databases can produce pure xml result sets. Have to explore that.
• Browser support for xml, xsl. Have to study different browsers� (versions) functionality and mention which are the browsers suitable for the site.
• Major disadvantage : When a record is displayed in a page, reference to more than 3 records are given as a link. In order to display the text for that link, there should be as many client request (for those xml doc) as there r links. This repeated client to server transaction will not occur if the jsp formats the presentation instead of client side xsl.

Data entry

House keeping

• For each new/edited person record, see if there is a similar record
• Send a report about new records and other relevant details to the web admin

Choosing the classification

Misc

• In the personTable, can I use varray to hold spouse Ids? This will avoid a separate spouseTable.
• What is the webspace we need for this application?
• Is it legal, ethical to mention dowry range, so that matrimonial search can be done based on it?

Resolved issues

• What size should the personId be? (mediumInt or longInt ?)
  • mediumInt should be ok
• In the personTable, I am expecting most of the fields to be null for most of the records (fields like webpage, email, addr etc). Will storage be utilization be optimal in such case or is it advisable to have two tables (first table with vital info & auxiliary table with sparsial data)?
  • Ram: There will be minor a difference if you split the tables and the aux table will not have a row if all the cols in it are null. But it is not worth splitting the table. Go ahead with one if you expect only 50k to 100k rows.
• Will the table be indexed on primary key automatically or do we have to do that?
  • Ram: Table will be indexed on PK automatically. But you may want to add additional indexes based on the queried cols. e.g. Religion, age etc. this will improve the performance.
• What is index-only table?
  • Ram: IOTs are special kind of tables where data and index entries are stored in the same segment. (Normal tables will have different data and index segments). IOTs improve query performance. Generally used in look up tables which does not involve high dml activity but get large number of queries. If you have many dmls on this table the performance may get affected.

Notes

• HashMap seems to be suitable for holding the person detail
• LinkedList is ideal to implement lru algorithm for person pool