Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: Table structure for a contract/product database - please comment

    Im 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?

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    you have a problem concerning tbl_Products. You need to have a table that is tbl_ContractDetails and you need to have a table tbl_Products. ContractDetails will have FK to Contracts and FK to Products.

    How to handle products changes is situational and not every method is the same. For instance a price that is in the Product table generally transfers as data in the ContractDetails table because that product price will change and you don't want it to ripple thru all past details. But if the Product is modified more profoundly - then one creates an entirely new Product with a new ID - so it can not be confused with the old version.

    Managing inventory - is entirely different. One would rely on these tables for data ; but tracking in-stock quantities would be a totally separate table.

    Importing Excel is a strength of Access - it is routinely done. Not sure about the 5 sheets for one table question....as I don't understand what you mean.

  3. #3
    Join Date
    Feb 2010
    Posts
    3
    NTC - thank you for your comments.
    I think I know what you mean. I make a kind of head table called "contract", and then two tables called tbl_contractdetail and tbl_products. I just can't figure out, what should be the FK in tbl_products? Could it be suppliernumber_productnumber...

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    anytime I see something like valid hospital 1,2..4 I start to worry.
    that suggest to me that those elements should b e pushed down to another table/s. in this case that has the feel of tow entities as hospital entity and in intersection table which associates what products are valid for what hospitals
    because you want historical price data then you need to push that sort of data down to a sub table, identified as say product and datebanding as the PK. or prodcut and datefrom.

    avaailbility depends if you are concerned with group stock or stock at each hospital (if each hospital then you could store that in the intersection table.

    anoither thing you may need to consider is product equivalence. ie depeding on the supplier we buy prodcut A or product B. again it depends on your business requirement, whether say there is only ever one replacement/alternative product or many. if there is the chance there could be more than one equivalent product then you may need an intersection table which identifies which product is an equivalent for another

    don't understand why postcode entity exists

    you may need to look at an entity to handle quantities for equivalence purposed

    eg
    1 (real) gallon = 4.54 litres = 1.2 micky mouse gallons (US)
    or 100g = 1Kg and so on.
    within the metric system measurements are pretty consistent, but the old Imperial & US systems can be very very confusing.

    if you design the app appropriately you could do away with the buyers use of Excel spreadsheets, and get the app to spit out an email to the buyers or use a form so they can identify what items are running low. indeed you coudl actually do away with the buyers altogether by getting the app to send purchase orders, assuming you can rely on someone entering the data correctly and maintaining the stock levels correctly.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2010
    Posts
    3
    thank you healdem for your answer. I'm getting more and more confused :-)

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one of the problems you are going to hit by asking questions in forums such as this is that we don't know the scope of your project. we don't know what is included or excluded, we don't know your deployment schedules, budget and so on. so everyone has their own perspective on a data model. rarely is a model the same from different people

    if you are getting confused, then go back to basics, thoroughly understand the concepts of normalisation
    Fundamentals of Relational Database Design -- r937.com
    and
    The Relational Data Model, Normalisation and effective Database Design
    are thought of as good primers on this site
    the aim of the game is to avoid repeating infroamtion or logical errors
    so unless you know there are only ever going to be 4 hospitals then don't build in artificial constraints such as hospital 1..4, if you have to store history then you cannot store price at product level because by definition the price varies over time.

    there are complexities if you have a different price for each hospital or sections within a hospital. bear in mind that its possible you may get special offers on products which are being used for a specific purpose.. so you could get a specific drug which is being trialled in a department at one price and elsewhere its another price

    bear in mind with suppliers you may discount over rides which you may or may not pass on to the customer. you may or may not spend targets with specific suppliers, ie spend X get y back as a rebate so close to the year end you need to make certain you hit those rebate targets. otherwise it can hit cashflow hard at year end when the beancounters are grubbing around for more profit
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2009
    Posts
    340
    it really isn't complicated.

    The main record has ID ABC.

    That main record may have parameters of variable count/quantity; lets say the sales detail: 3 cans soup
    2 bottles milk
    etc.

    That is in a separate SalesDetails table and as long as each record has the cross reference of ID ABC - they will always tie together.

    That's it. It isn't unusual to have several cross referencing parameters tying to one main record - but it is always the same principle....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •