Some Ideas In Database Design

Question

I am creating a site where users can sell their products(let say a little version of eBay, just assumption). Concerning the database design am a little bit stuck and looking for some ideas how it is done in real world.

My initial thoughts

1. I wanted to create one database for each user (seller). But I realize that if let say 70,000 people (70,000 is just assumption) want to sell, then 70,000 databases must be created. I think this may not the right idea. Or?

2. I am also thinking of creating one database for all sellers. I don't know if it is the best thing to do.

Questions

1. My question is what is the best way of designing a database for the above purpose?

2. Sites like eBay, how do they design their database for such large people to sell? Do they use one database, or each user has his own database?

   I have googled "eBay database design" but the results i got were not what i was looking for. The results were too high level(not how they designed their database).

3. Is it advisable to create for each user a sub-domain?

A pointer to where I can find how such systems are designed will be appreciated.

I hope someone can help me with some specific answers. Thank you

Answer 1

As a general rule, and speaking very broadly, each "thing" in a database is represented by a row in a table. Each seller is a row in a table. Each purchaser is a row in a table.

The simple, obvious, and arguably wrong approach is to have one table of sellers and another table of purchasers. The problem is that one real-world human can be both a seller and a purchaser. In other applications, one real-world person might be a doctor, a patient, and a hospital board member, all at the same time.

The problem with those two tables is that an update to, say, the email address for me in the sellers table might not be matched with a corresponding update to my email address in the purchasers table. And, in fact, that's almost certain to happen, because the row that corresponds to me in the sellers table probably won't have the same primary key as my row in the purchasers table.

For a working solution to the general case, see this SO answer. In your case, you're only talking about people. If you want to model people, sellers, and bidders, you're probably looking for something along these lines. This kind of structure allows one person to be both a seller and a bidder. (I think bidder is a better word than purchaser, if we're going along eBay lines.)

create table people (
  person_id integer primary key,
  full_name varchar(35) not null
);

create table sellers (
  person_id integer primary key references people (person_id),
  other_attributes_of_this_person_as_a_seller char(1) not null
);

create table bidders (
  person_id integer primary key references people (person_id),
  other_attributes_of_this_person_as_a_bidder char(1) not null
);