Desperate need of help making A database

[garebear]

Hi I am an absolute newbie and am trying to make a database for the final project for one of my classes. any help would be soo much appreciated. My first question has to do with what i should make for tables. Right now I have tables for the product, seller, customer, concessions, and concession customer. Below is the assignment are there any other tables I should add in.
Thank You soo much in advance.

Jane Benoyt, a famous Olympic athlete turned entrepreneur, collected many things on her travels and now is sick and tired of them and wants to sell them. She figures there are a lot of people who are in the same predicament so she wants to start an auction website. Before she gets to the web, though, she wants to test market her database around her and a small group of othersÕ collections. She wants to track the different kinds of products (furniture, jewelry, collectibles, glassware, art and comic books), the manufacturer of the product (i.e. Tiffanys, Marvel Comics, etc), seller of the products with their vital statistics, the average rating of the seller (A-F, A being the best). She wants to know how many products that each seller sells on her site.

She needs to know the top sellers in each product category and show which geographic area these people are from. She wants customers, who need to have their own listing with name, address, and usual method of payment, to be able to post comments about each of the sellers. As far as the products being sold, along with the product category, there should be a place for people to put bids on the product, a place where people can buy the product outright with a fixed bid amount and a place to describe the product thoroughly with a picture of the product. An email address needs to be placed with each seller and each customer.

She decided that because of she has excess stuff; Jane is also going to open a collectibles store called Got Your Stuff. Individually coding her in-store customers with ID cards, she was excited to find out who visited and/or bought from her, what their names and addresses/phone numbers were, what category of collectible they were interested in, and the frequency of visits. She also sells candy, bottles of soda and water and popcorn to the patrons and wants to know these productsÕ sales and cost figures.

 

[Trevor G]

Welcome to the Forum,

You can look at YouTube videos on how to create an Access Database

Heres a start, you then need to look for building relationships and then queries. That should get you going for a while.

 

[garebear]

yeah thats what i have been doing. My main problem is that im not sure if I need to add more tables or not

 

[Trevor G]

What have you done so far, it will always be useful to indicate which version of MS Access you are using.

Based on your first thread I would suggest you need 3 tables.

Customers, Products, Sellers. There maybe options later to have a couple more tables, but in the first place read what you have been asked to do and pick out the parts, list them on paper to each table and then start from there.

One idea is draw 3 boxes, place the field (heading names) in each box to ensure you are listing everything that is in your assignment then start build the tables, think about the data types and any properties you might need to adjust. Don't place tsting data in as yet as you will need to build the relationships which may cause issues if you have tested each table before they have been related.

Having said that you could take a copy of the database before the relationships and then populate each table with some data to see if what you have created looks correct on the eye as you fill it up.

 

[John Big Booty]

Dude you start out by claiming to be a complete noob, yet you are asking for help to do your final project. What have you been doing for the rest of the year

No one here is going to do your assignment for you. However if you have a specific question, you will get plenty of assistance.

 

[Mr Smin]

Pretty harsh that your final project is a full sales database with customer analysis, yet it seems they haven't prepared you with simpler projects (as you said you are a newbie). I would raise a complaint with the institution you are studying at.

 

[garebear]

Right now I have tables for the product, seller, customer, concessions, and concession customer.
I was trying to figure out what I should do with where it talks about needing a place to place bids and to buy the product outright. I was thinking of making that into a table, does that seem right?
I am using ms access 2007

 

[Trevor G]

I would think that the bidding part would be the web base part.

It maybe useful to have a separate table if you want to keep history of each bid.

 

[garebear]

It maybe useful to have a separate table if you want to keep history of each bid.[/QUOTE]

Thanks that is what I think I may do.
Another problem I am having is that when I try and make a relationship between two tables it wont let me enforce referential integrity

 

[Trevor G]

The relationships can be a tricky thing.

You will need primary keys in some tables (although not essential at times) and then relate to them in others. If you have had data in the tables already this is where they may be a few problems.

What are the field names and datatypes in each table you are trying to relate to.

For instance

ProductID which is Autonumer and set as primary would relate to SellerID as Number field no primary.

 

[garebear]

Here are the tables I have I was trying to relate product and seller as a one to many relationship

Customer(CustomerID, LastName, FirstName, street, city, state, zip, UsualPayment, SocialNum, CustomerEmail)

Product (ProductId, manufacturer, type, condition, SellerName, ProductBid, ProductFixedBid, ProductDescription, ProductPic, sellerID)

Seller (SellerID, SellerName, AverageRating, address, city, zip, numofproducts, SellerEmail)

GYSCustomer(GYSCustomerID, GYSFirstname, GYSLastname, GYSStreet, GYSCity, GYSState, GYSZip, GYSPhoneNum, GYS CustomerInterest, GYSCustomerVisits)

 

[Trevor G]

Can you upload a sample of the database, if over 2mg you will need to zip it. I am around for an hour or so, then out and about.

 

[garebear]

how do I zip it?

 

[Trevor G]

Using windows explorer find the database and then use the right mouse button, then look for send to and in the send to you should have compressed file.

 

[garebear]

got it thanks I had no idea it was that easy

 

[Trevor G]

OK the reason why you can't relate this is becuase you have data already in the tables, therefore there will be conflicting ID numbers so they can't meet each other.

As I have already mentioned. If you take data out then you can build the relationships to each table no problems.

One thing to suggest as well is that you should follow a naming convension for tables, queries, forms and reports as this is a common practise in business.

The general principals are
Tables = tbl
Queries = qry
Forms = frm
Reports = rpt
Macros = mmm or mcr
Modules = mod or bas

I wouldn't change anything now but for your next database.

If you want some extra points on your assignment, then think about adding a help facility, now this is easy to do as you would use a word document with bookmarks and a table of contents then convert to html and place in a windows browser object on a form there is a tutorial on this from the databapig website. Here is a link. scroll down the list until you see Creating a Custom Help File System for your Application


http://www.datapigtechnologies.com/AccessMain.htm

 

[garebear]

ok so you are saying i should build my relationships before I put in all the data and thanks so much for helping

 

[Trevor G]

Yes because if you start populating first and are creating records the relationship fields can't match unless you edit after you have created the records then try the relationships. So example if 2 tables need to be related one called Staff and One called Product you have staffID in the Staff table and StaffID In the product table you fill out the staff table and the StaffID is generated automatically as it is primary with Autonumber in the Product table no Staff ID gets added unless you add them and if you do and they don't match the staff table no relationship can be created.