SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Something is WRONG with my database design, Please Help REDESIGN it.

    what I want is for employees from "employee" table to be able to enter inquiries into the "enquiries" table, about products
    from the "Products" table. The "companies" table hold the list of approved companies that can make inquiries.
    The "employees" table holds the list of employees from all companies that are allowed to make inquiries and give answers
    to inquiries. The "status" table is the status of the product.
    Finnaly, I would like to know the (a) time inquiry was received (b) which company employee and company made the inquiry
    (3)Which employee was this inquiry directed to (4)the product that was inquired about.
    Looking at my tables and relationships, I feel something is missing.
    ------------------------------------------------------------------
    companies
    ----------
    companyId
    companyName
    address
    phone
    contact_person
    productId

    products
    --------
    productId
    productname
    dscription
    availabile_date
    expiration_date
    status
    inquiryId
    statusid
    inquiries
    ----------
    InquiryId
    enquirer
    inquiry
    inquiry_date
    inquiry_time
    response_date
    response_time
    comments

    Employees
    ---------
    empid
    fname
    lname
    phone
    email
    position
    companyid
    status
    ---------
    statusId
    available
    retired
    -----------------------------------------
    I have a sick feeling this is a poor design, Please help me fix it.
    Please redesign it if possible.
    thanks
    logic

  2. #2
    Old Folks 127.0.0.1 pacres's Avatar
    Join Date
    Sep 2003
    Location
    ed.ab.ca.na.ea
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    companies
    ----------
    companyId
    companyName
    address
    phone
    contact_person

    products
    --------
    productId
    productname
    dscription
    availabile_date
    expiration_date
    status

    inquiries
    ----------
    InquiryId
    ProductId
    CompanyId
    EmployeeId
    enquirerContact
    inquiry_date
    inquiry_time
    response_date
    response_time
    comments

    Employees
    ---------
    empid
    fname
    lname
    phone
    email
    position
    companyid

    status
    ---------
    statusId
    statusDesc
    -----------------------------------------

    Try this. I have taken out a few fields and placed them into your Inquiry table. You should have better data management with this database. You are on the right track but a few of your fields are out of place.

    Basically you will want to add new companies, status, employees, and products to your database without relying on other tables(with the exception of employee ?) A company can exist without a product, and a product can exist without an inquiry now. The inquiry table includes references to the other tables via an id. This will reduce data duplication.

    I'm very good at explaining things so I hope someone can come by and clean up my explanation. You may also want to read up on Relational Database Development.

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Something is WRONG with my database design

    Thanks. I knew something was wrong with it 'cos I was not geting the results I expected when I ran qureries.
    assigned


Bookmarks

Posting Permissions

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