CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2006
    Posts
    216

    Help me create database schema (just a few sentences scenario)

    I need to decide what should be proper relational database schema for the following scenario:

    There is a computer sales and maintenance store. The store’s services include computer sales, repair computer parts and installing and
    upgrading software and hardware.

    The store has a number of branches.
    There are a number of personnel working in each branch.
    Personnel can be reassigned to other branches to work.

    The database should include at least information about the following:
    • Details on sales and services provided
    • Details on employees
    • Inventory details
    -----------------------------------------------------------------------------

    I made the following tables:
    Branch {branch_id, manager_id, name, address, phone_no, email)
    Employee {employee_id, name, age, date_of_joining, address, phone_no, email, salary, expertise}
    Service {service_id, description}
    Item {item_id, name, description, brand, model, cost}
    Branch_Service {branch_id, service_id}
    Branch_Employee {branch_id, employee_id}
    Branch_Item {branch_id, item_id, quantity}

    The first field listed of each table is primary key.
    Relationships between tables
    1. branch_id of Branch_Service is foreign key of branch_id of Branch.
    2. service_id of Branch_Service is foreign key of service_id of Service.
    3. branch_id of Branch_Employee is foreign key of branch_id of Branch.
    4. employee_id of Branch_Employee is foreign key of employee_id of Employee.
    5. branch_id of Branch_Item is foreign key of branch_id of Branch.
    6. item_id of Branch_Item is foreign key of branch_id of Item.
    ------------------

    Could you please check and suggest any improvement in it? I am not sure if tables will provide information about Inventory details properly.

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: Help me create database schema (just a few sentences scenario)

    You find out what is missing by adding some data and see if you can pull the data back out in the manner you expect.

  3. #3
    Join Date
    Oct 2006
    Posts
    216

    Re: Help me create database schema (just a few sentences scenario)

    @Arjay, thanks but can you tell if the schema is proper for the given scenario? Or, do you think I need to make any changes, such as adding any more tables, changing any table or changing any relationship between tables.

  4. #4
    Join Date
    Oct 2006
    Posts
    216

    Re: Help me create database schema (just a few sentences scenario)

    I added some data and I am able to pull back. But it is possible that I have not been able to come up with all important queries that one may seek from the database. I thought of the following important queries: -
    1. List ids, names of branches and numbers of employees working there in descending order of the number of employees.
    2. Name the branch where an employee with the given name works.
    3. List branches providing a given service.
    4. List services provided by a given branch.
    5. List branches together with the total cost of all items in each branch.
    6. List branch names together with names of branches.

    Not sure if should add any more.

  5. #5
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: Help me create database schema (just a few sentences scenario)

    The first thing to do is to pull together a list of use cases to determine how the user is going to use the system.

    The use cases typically include the data the user wants to see.

    From that list, you would design the tables to include the data and build up the list of queries where the user can access the data.

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
  •  





Click Here to Expand Forum to Full Width

Featured