New Database Design - Advice Please

[nancybrown]

I'm mentally preparing a new database . . . I'm about 3 years rusty and have lost my bearings some-what.

My database will be a financial database and will probably be placed on a shared drive.

The purpose is twofold: Compare 2008 vendor payment terms and spend against 2009 vendor payment terms and spend. The unique identifer is the vendor ID.

My plan is to create a table for the 2008 terms that lists line items by monthly spend and upload the 2008 data from excel. I will be importing 2009 terms in excel or csv (whichever is better) on a weekly basis to run queries and compare spend, terms, etc.

The design confusion: the 2008 is currently an excel with 128,000 line items; I'm guessing the 2009 will probably exceed that as the year goes on and new vendor numbers are created on a regular basis in our accounting system.

Stumped:
1. Am I right in using the Vendor ID as my primary key?
2. How would I design the table for Vendor IDs as there are new vendors added in our accounting system regularly?
3. Should I create 3 database: 2008 Payment Terms and 2009 Payment Terms (FE), queries, etc., (BE)?

I'm really stumped as to how to begin.

Help Please!!!!

 

[Norie]

Buck

If you are splitting data between a front-end and back-end then normally all the back-end should contain is data, no queries/reports/forms etc

And you appear to be implying that you want separate tables for separate years - bad idea.

If the structure of the data for each year is the same then have 1 table with a field indicating which year each record is associated with.

If you need to get only data for a particular year just use a query with the appropriate criteria.

 

[SydneyGeek]

I'll second Norie on that.

If the only difference between the tables is the year, just add a PaymentYear field to differentiate them. The number of records in the table shouldn't be an issue; I have used tables with over a million records.

Keep the Vendors table as it is, and link the VendorID to the Payments table as you planned.

Denis

 

[nancybrown]

Here is really what I will be doing with the database: I have 2008 payment terms (naturally they do not change). On a weekly basis, I will need to run reports for 2009 payment terms YTD and compare them against the 2008 payment terms; unique identifier is the vendor ID in both tables. I need to constantly replace the 2009 terms so I don't have dollar amount being added to previous 2009 amounts. So I had planned to just delete the old 2009 and replace with the weekly updates . . . this is probably not the way to do it, but I'm lost!!!

My other concern is that the 2008 vendor IDs will not change; however, the 2009 vendor IDs can update regularly . . . if I run a query to compare 2008 to 2009, what happens to the new vendor IDs that appear in 2009, but not 2008?

I've built some elaborate dbs, but can't seem to get myself focused on this one. Any ideas would be helpful and a life saver!!!

 

[nancybrown]

another issue is that each database will have multiple line items for one vendor id.