Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012

    Question Unanswered: Database design help, please!

    I have messed around with my database , but still didnt get good and optimized structure. Currently I did one big table hundreds of duplicated records with it (each line for each event: add, loan, removed, mainten. And some more than once)

    Please can anybody give some good advice how to solve this quite simple looking project
    Problem description:
    I have 1..N warehouses. (scalable)
    1. Each warehouse has products in it (all with same parameters).
    2. There are 2 type of products depend of lifetime.
      • Best before (starts ticking when added to database)
      • Usage time in years (starts when product will loaned out to someone).
    3. Products are in groups (2-level products tree Group->Product)

    Events that can occur with product:
    ADD: set product params + number of product 1..N, time, place, payd_sum, Paym.method, descr of payment. Ps. You can add new product, but you can increase existing product count (new shipment). If you increase number, then every this shipment can have his own lifetime parameters.
    MOVE: from one warehouse to other (1..M products)
    LOAN: loan out 1..X products to person (time) (persons list is in table t_person)
    MAINTENANCE: Any product can be in maintenance: loaned or in warehouse (maintenance start time, description and duration)
    END_OF_EXPL.: time, reason (no removing from db table)

    My problem is that I cant design properly that when I buy by example 100 new products and put in Warehouse A (with lifetime N). How to store information for that I put 25 products to Warehouse B, 2 products loaned out to person X and 3 to person Y and 10 are in maintenance. And after some time Warehouse B sent some products back to Warehouse A. And after some time added more of same products to warehouse A (with other lifetime). etc etc...
    How to save all this kind activity in normalized way? So I can any time ask from database how many items are in Warehous A, B, ..., how many products are in maintenance total and in each warehouse. How many products in which warehouse is over due ( by best before time, by loaned out product lifetime, and maintenance duration ended...) etc etc.
    When somebody can help with better and normalized structure I can do other queries to get all those required info.

    Sorry, I'm not wery good with databases. I can do php and other programming, but database designing makes some difficulties..

    Sorry my bad english...

    Thank You for any help!

  2. #2
    Join Date
    Mar 2012
    Hi progeja,

    Welcome to DBForums. I see this is your first post.

    For starters, could you list your tables and the attributes of each table, without explanation?

    It sounds like you need to learn some basic database design concepts. Do you know about primary and foreign keys? Normalisation?

    Perhaps you need to consult some tutorials on database design. I don't know of many but Googled it and found this (don't know if it's too technical for you): DATABASE DESIGN STEPS / HOW TO DEVELOP A DATABASE - a knol by David McCaldin

    But there are surely other, more methodical tutorials.

    If you list your tables and attributes I may be able to help.


Tags for this Thread

Posting Permissions

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