Designing an ebay type database

[jackdaniels007]

Hi, i was wondering if someone might be able to help me. I currently sell paintings using an auction format in hotel suites.

I wish to expand this and operate on line. I'm currently designing a database and need some help on the design.

Requirements:

Different levels of access for Admin Staff and public users.

Sellers and buyers will submit there own details and have a password.

So far i have...........

USERS
user_id
title
first_name
second_name
house_no
street
town
county
postcode
phone_no
date_reg
last_login
user_level
activated (When reg, an email sent for activation via link in email)
password

PAINTINGS
paintid
type
artist
year
description
reserve_price
condition
signiture
current bid
bid_history
no_of_bids

AUCTION DETAILS
paintid
userid
commision
price_Reached
net_Amount_for_Seller


I'm not sure if this will work, or if i have designed the database wrong.

I also want a tracking section that will show current highest bid, number of days left, history of all previous bids, number of bids to date and archive all this after the auction has ended. I've tried adding this to the above. Should i get rid of the AUCTION DETAILS and add this to PAINTING? What would you reccommend?

Any help or pointers would be excellent

Many thanks

[skhanal]

In Paintings Entity,

you will have to move current_bid, bid_history and no_of_bids to a separate table (bid_history) as

bid_id
painting_id
userid
bid_amount

This will keep track of all the bidding. the primary key will be first 3 columns. You can add other columns like datetime and any other bid related attributes there.

The Auction_Details then can capture the final transaction details. You will need bid_id in it to refer to bid_history.

You need bid ending date in paintings.

current highest bid, number of days left, history of all previous bids, number of bids to date can be derived from existing columns.

[jackdaniels007]

Thank you very much for your reply, you have been a fantastic help. I just brought a book from amazon on normalisation, but i don't really understand it. If i made the amendments you mentioned, will the 4 tables be normalised? If so what level and what would each level look like to aid my understanding.

Thanks once again, your help is very welcomed

[skhanal]

It should be atleast 3-rd normal form. The book may have example for each level. The 3rd normal form mantra is

"The key, The Whole Key and nothing but the key. So help me Codd."

That means

1. All elements of a row (record) are dependent on the unique key of the row.

2. All elements were, in the case of a composite key, dependent on the whole composite.

3. All of the elements of a row were dependent ONLY on the key and not on the other elements of the row.

[jackdaniels007]

Hi, thank you very much for that. I started from the beginning and have done what you said starting with UNF, to 3NF. The book basically said what you mentioned, but you said it in a very easy to understand way. I had a lot of trouble though. But was wondering if you might be able to check out what i have done? The book example uses intruments, so i did it on that. So the book is basically the same as what i'm doing with paintings. With 2NF, all non-key attributes are dependment on the whole key. So would instrument type be dependent on just instrument id and not user id? If so i would have to split this up.

UNF

User id(PK)
Title
First name
Surname
House no
Street
Town
County
Post code
Phone no home
Phone no work
Date of reg
Last login
User level
Activation
Password
Instrument type
Model
Year
Place
Serial no
Condition
Description
Reserve
Price reached
Price paid to- seller
Commission
No bids to date
End date
Current highest-bidder
Previous bids

1NF

User id(PK)
Title
First name
Surname
House no
Street
Town
County
Post code
Phone no home
Phone no work
Date of reg
Last login
User level
Activation
Password

Instrument id(PK)
User id*(FK)
Intrument type
Model
Year
Place
Serial no
Condition
Description
Reserve
Price reached
Price paid to- seller
Commission
No bids to date
End date
Current highest- bidder

Bid id(PK)
Instrument id*(FK)
User id*(FK)
Previous bids
2NF

User id(PK)
Title
First name
Surname
House no
Street
Town
County
Post code
Phone no home
Phone no work
Date of reg
Last login
User level
Activation
Password

Instrument id(PK)
User id*(FK)
Instrument type
Model
Year
Place
Serial no
Condition
Description
Reserve
Price reached
Price paid to- seller
Commission
No bids to date
End date
Current highest- bidder

Bid id(PK)
Instrument id*(FK)
User id*(FK)
Previous bids

3NF

User id(PK)
Title
First name
Surname
House no
Street
Town
County
Post code
Phone no home
Phone no work
Date of reg
Last login
User level
Activation
Password

Instrument id(PK)
User id*(FK)
Instrument type
Model
Year
Place
Serial no
Condition
Description
Reserve
No bids to date
End date
Current highest- bidder

Instrument id*(FK)
User id(FK)
Price Reached
Commission
Price Paid to- seller

Bid id(PK)
Instrument id*(FK)
User id*(FK)
Previous bids

Your help is once again very welcomed. I feel i'm actually getting somewhere now and learning.

[skhanal]

Is userid here same as current highest bidder?

Instrument id(PK)
User id*(FK)
Instrument type
Model
Year
Place
Serial no
Condition
Description
Reserve
No bids to date
End date
Current highest- bidder

[jackdaniels007]

The current highest bidder is the user with the highest bid, this will change with each bid that is higher than the previous. The user id is a link to the person who owns that instrument.

[jackdaniels007]

Hi, just realised something. I think i should remove table 3 completely in 3nf.

Instrument id*(FK)
User id(FK)
Price Reached <------can be taken from the bidding table
Commission <-----can be worked out
Price Paid to- seller <-----can be worked out

the 4th table for 3nf should say bid, not previous bids.
i think current highest bid and no of bids to date should be removed to, as this can be taken from the bid table i think.

[jackdaniels007]

3NF

USERS TABLE:-
User id(PK)
Title
First name
Surname
House no
Street
Town
County
Post code
Phone no home
Phone no work
Date of reg
Last login
User level
Activation
Password

INSTRUMENTS TABLE:-
Instrument id(PK)
User id*(FK)
Instrument type
Model
Year
Place
Serial no
Condition
Description
Reserve
End date

BIDDING TABLE:-
Bid id(PK)
Instrument id*(FK)
User id*(FK)
Bid
DateTime

I think this is now correct? I think, lol. I also think it may be in 3NF, lol. Any idea's?

Many thanks

[skhanal]

Looks good to me. Best way to judge is by playing with sample data.

[jackdaniels007]

Hi, thank you for all your help. Everything seems to be working ok.

I have another question. In this database above, there will be buyers, sellers and admin staff using the database. In many databases there would be seperate entities for all three and this would be reflexed in the database. In my case i put all three entites together and called the table Users. In users i have set up a field called user_level, this sorts out who is who.

Would i be correct in doing this? What would you personally do?

Many Thanks

[skhanal]

I would do the same if they have same attributes. In ER diagram you can show this as sub type relation

user with three sub types buyer, seller and admin. If you want to capture different attributes for each type then your user table can become very wide and only some attributes will be applicable for each type. If this is the case, you can have all common attributes in user entity and specific in separate entities which relates to user by ID.