-
October 14th, 2014, 12:04 PM
#1
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.
-
October 14th, 2014, 06:01 PM
#2
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.
-
October 14th, 2014, 09:32 PM
#3
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.
-
October 14th, 2014, 09:35 PM
#4
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.
-
October 15th, 2014, 12:17 PM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|