I am Phani Sridhar Edala a Database Programmer Analyst who works for a Direct Mail Company
called USA-Direct Inc. We are developing a fund raising application and we came across a
technical issue regarding Database Design and I hope you can show us solution for it.

The issue is

Nouns in the given Problems

1.Campaign
2. Packages (Different types packages with in a campaign)
3. Segments (Different types of segments with in a Package)
4.Mailcodes (Different types of mail codes with in a Segment

Verbs for the given Noun

Campaign
Campaign Code
Description
Start Date
End date
Active Flag
Goal

Package

Package Code
Campaign Code
Description
Start Date
End date

Segment
Segment Code
Package Code
Description

Mail Code

Mail Code
Segment Code
Package Code
Description
Mail date
Mail Qty
Active Flag
Mail Cost

This is the Present Database Design
But Our Client Request is to have multiple Campaigns, multiple packages, multiple segments,
multiple mail codes that is fine and at the same time we should handle to have unique packages
with in a campaign but the same package code can exist with another package

For E.g.
Campaign codes are
C01, C02, C03, C04
Package Codes are
P01, P02, P03, P04
Segment Codes are
S01, S02, S03, S04
Mail Codes are
M01, M02, M03, M04

Taking this into picture

P01 can exist in all the campaign and at the same time should be unique with that specific
campaign

At the same time

S01 can exist in all the packages and at the same time should be unique with that specific
package
M01 can exist in all the segments and at the same time should be unique with that specific
segment

The whole Picture looks like Tree Structure like as hierarchical database Model


I would really appreciate if you could suggest some sought of model or a concept in achieving
this Issue or any sought of Implementation Techniques of having hierarchical structure in an
RDBMS.

Right now we are using Sybase SQL Anywhere Server as our database and Power Builder has Front
end Tool.

Please excuse me if you have hard time in understanding my words or sense

Thank you for your time
Any sort of help will be greatly appreciated

Phani Sridhar Edala
Database Programmer Analyst
USA-Direct Inc.
2901 Balckbrige Road
York, PA –1702
edalasridhar@hotmail.com
sri_edala@yahoo.com