Hello All,
I've been asked to create a database for one of our customers. The database will house project information (WBS element numbers, program name, division, cost, etc.). I'm not sure the best way to go about this with the information I have to work with.
For example:
One data pull I do (i'll have to pull this on a weekly basis and add it to the access database) will contain the full WBS element number (6 levels) and a bunch of financial data columns as well. To that dataset I'd like to pull from tables in order to add the program name, division and other information specific to that WBS number.
My research on database design says that it'd be optimal to have a separate table each for programs, names, divisions etc (as opposed to one table with a WBS element number and the division, program, etc.). The WBS structure is such that the division is specified in the 2nd level of the WBS, program name in the 3rd etc.
The problem is that the tables are related by the WBS number, but the programs table would only go up to 2 levels, whereas the data table would contain the full WBS number. I'm assuming I can't define a relationship between the two tables such that the first 14 characters of the WBS element in the programs table match the first 14 characters of the full WBS element in the data table, correct?
If that's true, is it also correct that in this case I must create one table with all the full WBS elements and the corresponding program names, divisions etc.
Thanks for all your help! I'm sure you can tell I'm relatively new to both access and relational databases in general. Up to this point, all my database needs have been handled in excel and I haven't had the need to create a database with more than 15,000 records.