Hi,
I'm designing a simple database for filing system:
There are two levels of files (both look_up tables):
tlkpFile1, tlkpSubFile1 and a transaction table, tblFilings, for
filings (when documents ready for filings, user just pick the file name
from either look-up tables and insert to this table). tlkpSubFile1
includes files that are sub files from the files in tlkpFile1.
The design I have come-up with is like this:
tlkpFile1: tlkpSubFile1:
ID(PK) Name_File1 ID (PK) Name(FK_File1) Name_File2
1 Departmetns 1 Departments Marketing
2 Sales 2 Departments IT
3 Sales Jan
3 Sales Feb
My question is how should I design the tblFilings (transaction table)
with respect to those two look-up tables?? I'm thinking to have a
field, FiledIn, which will have a many-many relationship with both
fields in the look-up tables (Name_File1 and Name_File2)
tblFilings:
ID(PK) FiledIn
1 Sales **this from tlkpFile1
2 IT **this from tlkpSubFile1
MTIA,
Grawsha