Table structure for contract/product database - please comment

[Morten]

I’m working in a procurement department, which make a lot of EU-tenders every year. Now we need to track our contracts and especially the products contained in the contract. I have made the table structure below – please comment.

PK = Primary key
FK = Foreing key

[tbl_contracts]
contract_id (PK; Autonumber)
contract_number (FK)
supplier_number (FK) (number from The Central Business Register (CVR))
buyer_name
contract_title
contract_description
contract_type
contract__start_date
contract_end_date

[tbl_products]
product_id (PK; Autonumber)
contract_number (FK)
supplier_number (FK)
product_name
product_number
sales_unit
sales_price
valid_hospital_one (yes/no)
valid_hospital_two (yes/no)
valid_hospital_three(yes/no)
valid_hospital_four (yes/no)
product_start_date
product_end_date
product_version_number
produkt_status (if the hospital can buy the product; yes/no)


[tbl_suppplier]
supplier_id (PK; Autonumber)
supplier_name
supplier_number (FK) (number from The Central Business Register (CVR))
supplier_address
supplier_postcode (FK)
supplier_town
supplier_telephone
supplier_fax
supplier_email
supplier_www

[tbl_buyers]
buyer_id (PK; Autonumber)
contract_number (FK)
buyer_name
buyer_telephone
buyer_email

[tbl_postcode]
postcode_id (PK; Autonumber)
postcode_number (FK)
postcode_town

I have two few questions in the beginning.

1. I need to store historical data like price changes, new products, products no longer in stock etc. I have therefore made some columns in the tbl_products to secure this: product_version_number, product_start_date, product_end_date and product_status. Is this a good idea?

2. Our buyers store the data in one Excel spreadsheet, how can I import these data to Access. Do I need to create 5 spreadsheets one for each table?

 

[DCrake]

Your relationships should prove themselves when you import the data and test the results. But it all looks fine to me. As for your spreadsheet it depends on how it is laid out. The best thing to do is create named ranges for the different elements of the database then in Access use import named ranges when importing from Excel.