Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Feb 13 '12 at 5:13

This question came from our site for professional and enthusiast programmers.

2  
You don't want to Google for things like "eBay database design." Google for things like "large scale database design" and "database scalability." A book or two on the subject will also be very helpful. But to at least answer the original question... No, creating an entire database for each user is not a good design. –  David Feb 10 '12 at 13:45
    
Thanks for your reply. Googling for "large scale database design" or "database scalability" seems to me too generic. I wanted to be specific to get specific answers. –  Eddy Freeman Feb 10 '12 at 13:48

1 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
);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.