Advice: Tough Design Problem (newbie)

[Eric Tubbs]

Greetings,

I have come across a design problem which I cannot seem to figure out. After
looking though numerous books, internet sites and pouring through newsgroup
postings, I have come acroos a fork in the road. In the remaining text, you
will find the questions are broken up into different sections to make it
easier to understand.

I hope this type of question is okay to ask the group.

--------------------------
Here are all of the fields coming from the Program Roster Report: (70
columns of data being imported)

ProgKey
ProgNum
ProgName
ProgStatus
StartDate
RevisedStartDate
EndDate
ProgOrigMaxCap
ProgMaxCap
ProgMinCap
ProgEnrolled
ProgWaitlist
ProgReserve
ProgAvailable
ProgCanceledEnroll
ProgTransferOut
ProgCanceledWait
ProviderNum
ProviderName
ProviderLocation
OrderNum
WaitlistNum
OrderLastUpdate
OrderStatus
OrderCreated
GroupID
HouseholdID
HouseholdSalutation
HouseholdStreetAddress
HouseholdCity
HouseholdState
HouseholdZip
HouseholdCountry
HouseholdPhone
HouseholdAltPhone
HouseholdEmail
StudentID
Prefix
FirstName
MiddleInitial
LastName
Sufffix
LegalName
Citizenship
Dob
Age
MinorFlag
Gender
SmokerFlag
SpecNeedsFlag
SpecNeeds
Roommate
RoommateID
Email
Accommodation
LegacyID
Transportation
Performance
DepartureCity
CarrierCD
ODCCity
Materials
Insurance
Generation
ProgID
ShipToStreetAddress
ShipToCity
ShipToState
ShipToZip
ShipToCountry

--------------------------
These fields are broken up into 6 distinct objects:

Programs
Program Dates
Choices
Providers
Hosteler
Household

--------------------------
From the objects above, the fields can be separated into the different
tables as shown below:

tblPrograms
-ProgramsID (surrogate pk)
-ProgNum
-ProgName
-ProgStatus
-ProgID (non-key; not every program has an ID)
-ProviderNum (fk to tblProvider)

tblOrders
-OrderNum (natural pk)
-WaitlistNum
-OrderLastUpdate
-OrderStatus
-OrderCreated
-GroupID (non-key; pertains to group travel)
-StudentID (fk to tblHosteler)

tblOrderDetails
-OrderDetailID (surrogate pk)
-ProgramsID (fk to tblPrograms)
-OrderNum (fk to tblOrders)

tblHosteler (a student)
-StudentID (natural pk)
-Prefix
-FirstName
-MiddleInitial
-LastName
-Sufffix
-LegalName
-Citizenship
-Dob
-Age
-MinorFlag
-Gender
-SmokerFlag
-SpecNeedsFlag
-SpecNeeds
-Roommate
-RoommateID
-Email
-HouseholdID (fk to tblHousehold)

Roommate & RoommateID have already been put into separate table

tblProgramDates
-ProgramDateID (surrogate pk)
-ProgKey
-StartDate
-RevisedStartDate
-EndDate
-ProgOrigMaxCap
-ProgMaxCap
-ProgMinCap
-ProgEnrolled
-ProgWaitlist
-ProgReserve
-ProgAvailable
-ProgCanceledEnroll
-ProgTransferOut
-ProgCanceledWait
-ChoiceID (fk to tblChoices)

For field names, ProgEnrolled to ProgCanceledWait, would placing these
fields into a separate table provide a way to track changes of enrollment
status/numbers?

tblChoices
-ChoiceID (surrogate pk)
-Accommodation
-LegacyID (non-key; tied directly to Accommodation)
-Transportation
-Performance
-DepartureCity
-CarrierCD
-ODCCity
-Materials
-Insurance
-Generation

tblProviders
-ProviderNum (natural pk)
-ProviderName
-ProviderLocation

tblHousehold
-HouseholdID (natural pk)
-HouseholdSalutation
-HouseholdStreetAddress
-HouseholdCity
-HouseholdState
-HouseholdZip
-HouseholdCountry
-HouseholdPhone
-HouseholdAltPhone
-HouseholdEmail
-ShipToStreetAddress
-ShipToCity
-ShipToState
-ShipToZip
-ShipToCountry

Should ShipTo information be put into a separate table?

Can you see anywhere for the rest of the fields where the data could
possibly be normalized further?

--------------------------
One of the hard parts that I cannot figure out yet is this statement in
their data specification sheet, "PROG_NUM, START_DT, ORDER_NUM, STUDENT_ID
form a unique key for roster records."

Does this mean that all of four of these fields should be in the
'tblPrograms' table as a composite primary key or would this be relevant
only to the CSV data file (for their online data system)? If the former is
indeed true, would the following table structure shown below accommodate
this situation?

For OrderNum, I cannot figure out the correct layout between tblPrograms,
tblOrders and tblOrderDetails. I have changed the design to reflect the four
fields above and a surrogate primary key). Any suggestions on how the above
situation can be accomplised??

tblPrograms
-ProgramsID (surrogate pk)
-ProgNum (composite pk)
-StartDate (composite pk)
-OrderNum (composite pk)
-StudentID (composite pk)
-ProgName
-ProgStatus
-ProgID (non-key; not every program has an ID)
-ProviderNum (fk to tblProvider)

tblOrders
-OrderNum (natural pk)
-WaitlistNum
-OrderLastUpdate
-OrderLastUpdateTime
-OrderStatus
-OrderCreated
-GroupID (non-key; pertains to group travel)
-StudentID (fk to tblHosteler)

tblOrderDetails
-OrderDetailID (surrogate pk)
-ProgramsID (fk to tblPrograms)
-OrderNum (fk to tblOrders)

tblHosteler
-HostelerID (surrogate pk)
-StudentID (fk to tblPrograms)
-Prefix
-FirstName
-MiddleInitial
-LastName
-Sufffix
-LegalName
-Citizenship
-Dob
-Age
-MinorFlag
-Gender
-SmokerFlag
-SpecNeedsFlag
-SpecNeeds
-Roommate
-RoommateID
-Email
-HouseholdID (fk to tblHousehold)

tblProgramDates
-ProgramDateID (surrogate pk)
-ProgKey
-StartDate (fk to tblPrograms)
-RevisedStartDate
-EndDate
-ProgOrigMaxCap
-ProgMaxCap
-ProgMinCap
-ProgEnrolled
-ProgWaitlist
-ProgReserve
-ProgAvailable
-ProgCanceledEnroll
-ProgTransferOut
-ProgCanceledWait
-ChoiceID (fk to tblChoices)

--------------------------
If I haven't stated this problem correctly, the data provider has a pdf
documenting their reports and the different field definitions. Upon request,
I will gladly send you the pdf document, if you need to look at it.

Many, Many TIA's

Eric

 

[Henry]

You design problem can be made much simpler if you break
down your database (DB) into manageable steps of design.
Designing a DB is usually performed from the output
desired, back into the DB entities. In other words, you
start with what you want out of the DB and work back to
what data you need to store to achieve this output. Then
you take the storage needs and assemble them into tables
(normalized). Now you have required data stored in a
normalized (logical) fashion. Next, you design the DB
logic to achieve data entry (manually or by import). Once
you have the data entry problem resolved you then design
the "how do I get the data out" into the required
format. Folowing these steps will help you get a handle
on the total program.
Iwill send separately a few articles that can help you
understand normalization of tables. After you figure out
what data you need to store normalization will help you
put the data into normalized tables and help you
establish appropriate relationships between tables.
Warning! If you do not normalize your tables first you
will have a very difficult time managing your data nd
extracting the data to achieve the end result you are
looking for.

Cheers,
Henry

 

[Henry]

Opps! Just discovered I can't attach something or send
to you direct.

If you want a copy of the articles I mentioned send me an
Email direct.
(e-mail address removed)
Or look up in your references about normalization.

Cheers,
Henry