Content

1. Aim
2. Functionality
3. Technology
4. Design
1. Table design
2. Class design
5. Web admin
6. Cost estimation
7. Issues

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

Technology

• Oracle 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,

	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
	}
}

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

Criteria for a good Webhost

• Web space
  • File space to hold html, script, class files
  • Database space
  • Runtime memory
  Are these size specification flexible?
• Number of ip addr (port) provided
• Number of schemas (users?) allowed in the database
• Backup, recovery procedure
• Software version
  • Jdk, jsp
  • Web server
  • Database
  • Other api
• Bandwidth, scalability
• Statistical analyses of nature and behavior of users

Development environment

• java 1.3
• Tomcat 4.0
  • JSP 1.2
  • Servlet 2.3
• Oracle 8 - Trying for a recent version (>= Oracle 8i)
• Ant 1.5.1 - a java based build tool from Apache
• JCreater - a java IDE from Xinox
• CoffeeCup - a html editor
• Winston Veerender, a friend, suggested Eclipse from www.eclipse.org as a java IDE; Will try that if it is more suitable than JCreater for this application.

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?

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.