[arcplus]

Help, I have been asked to design an Access relational database for a small bookstore that operates 3 stores. Books are sold by a sales person to a customer through an order. An order may consist of one or more books but assume that for a given order, all the books sold are all from one store and are sold by one salesperson.
Field name Field description (additional info given if needed)
Address Address of the customer
Author ID
Author Name
Book ID Unique id number for each book
Book type One of a number e.g. H for hard cover, S for soft cover etc
Book type description
City City the customer lives in
Country Country the customer lives in
Cust Id
Cust Name
Cust Type One of a number of values such as: I for individual C for Company
Cust Type Descr
Email Email address of the customer
On hand The quantity on hand, of a given book in a given store.
Order date
Order No
Phone no Customer phone number. Note: The customer can have more than 1 phone no
Phone type One of a number: e.g. H for Home, C for Cell O for Office etc
Phone type descr
PostCde Customer postal code
Publisher ID
Publisher Name
Quantity For each book sold it identifies the quantity of the same book (typically 1).
Salesperson Id
Salesperson Name
Salesperson Phone
Selling price The price can vary between stores as the the same book can be sold in different stores
Store address
Store City
Store email
Store No A value of 1, 2 or 3 representing the store number
Store Phone
Title Full title of the book

[Advertisements]

Are you asking for the database and the relationships to be mapped out for you?

You'll need several tables, depending on how normalized you want this thing...

Customer
Store
Employee
Book
Order

Stores can have many employees, an employee can only have 1 store (assumption)
Stores can have many orders, an order can only come from 1 store (your constraint)
Employees can have many orders, an order can only come from 1 employee
A customer can have many order, an order can only go to 1 customer

You'll also need a bridge table between Order and Book. A Book can be on many Orders, an Order can have many Books. Your bridge might be something like Book Order.

You were sorta vague, so I hope this gives you a good starting point. If you want to get more complex, you can normalize down by splitting Publisher and Author off of the Book table and into their own.

Let me know if this was at all helpful.

[Guse]

Are you asking for the database and the relationships to be mapped out for you?

You'll need several tables, depending on how normalized you want this thing...

Customer
Store
Employee
Book
Order

Stores can have many employees, an employee can only have 1 store (assumption)
Stores can have many orders, an order can only come from 1 store (your constraint)
Employees can have many orders, an order can only come from 1 employee
A customer can have many order, an order can only go to 1 customer

You'll also need a bridge table between Order and Book. A Book can be on many Orders, an Order can have many Books. Your bridge might be something like Book Order.

You were sorta vague, so I hope this gives you a good starting point. If you want to get more complex, you can normalize down by splitting Publisher and Author off of the Book table and into their own.

Let me know if this was at all helpful.