Hi,

I am new to the list and was wondering if I may ask for help with the following design please.

Here is some backround:

My project centers around validating tables in a data warehouse. The
tables in the DW are validated using a collection of validation files that
are produced when data is loaded into the DW. My business community would
like to see an inventory of the validation files and whether or not the
files have been received. To facilitate building an inventory, I have
built a table named tblFileSchedule. I plan on reading this table to
determine whether or not I need to make an entry into tblInventory. This
table gets information from a variety of smaller tables, designed to
support the scheduling or expected receipt of a validation file.

The design I have come up with is:

tblBaseTables tblValidationFiles
|-1 BTID (PK) |-1 VFID (PK)
| BTName | FLDID (FK)
| | VFName
| |--------------|
| |
| | tblBaseTablesFiles tblFileSchedule tblFrequency
| |M VFID (CPK) 1-------M VFID (FK) |-1 FrqID (PK)
|--M BTID (CPK) 1-------M BTID (FK) | FrqText
FrqID (FK)M-----|
WDID (FK)M----|
MthID (FK)M-| | tblWeekDays
FSHoliday | |--1 WDID (PK)
FSWeekend |---| WDText
FSSpecificDay |
| tblMonthly
|-1 MthID (PK)
METext

tblHolidayWeekend
HWID (PK)
HWText

The "options" tables contain:
tblFrequency tblWeekDays tblMonthly tblHolidayWeekend
Daily Sunday Last Bus Day Skip
BillDay Monday Last Cal Day Use Date
Weekly Tuesday Specific Day Back Up
Monthly etc ...

As it turns out I have the need for a linking table (tblBaseTableFiles).
If I understand correctly the two FK's make a composite PK.

Further each base table can have more than one validation file, and each
validation file for a specific base table can have different scheduling
options as to when it is expected.

My brain stall centers on two items:

1 - is there a simpler way to model this, to catch all the variations in
scheduling options?

2 - I am not sure how to handle tblHolidayWeekend. Business rules require
a value for both weekend and holiday processing using one of the three
values in the table. I don't think I can relate the two fields back to PK
in tblHolidayWeekend. I could set the data type of FSholiday and FSweekend
to a number, and store the selected PK value from tblHolidayWeekend that
way. Seems sloppy though.

Anyone have any insights?
Thanks very much.

Kevin