Design a Database for a Classified Adverts Website

Classified adverts are organised into classifications, for example motor cars, rooms for rent, baby

clothes etc. and each classification uses different features to describe items. Cars have make and

model, clothes have sizes, etc. Note that each user can only have one account – there is no need for

a buyer’s account and a separate seller’s account for the same person.

a. Items for sale in a classified advert system have different features depending on

their classification. Explain why this is a problem for a relational database and

suggest two possible solutions to the problem. Compare the two solutions, giving

advantages and disadvantages of each. Say which you would choose.

b. Design a set of normalised relations for storing data about people, objects for sale,

transactions and messages. For each relation choose a primary key and explain the

reasons behind the choice. Highlight the foreign keys in your design. Explain

whether your design is in second or third normal form and say why you chose that

level of normalisation. You needn’t include every possible field (don’t worry about

product features, for example), just show some example fields for each table.

c. Draw an entity relation (ER) diagram for the normalised relations you have designed.

There is no need to show non-key fields, but show primary and foreign keys and the

cardinality of their relationship. Clearly state any assumptions you have made.

d. Give an example of the SQL code needed to select the names of the buyer and the

seller of an example item if the item ID is known. The code should implement a join.

2. Now consider a NoSQL solution to the same problem.

a. Referring to your answer to part a. in question 1, explain how a document database

such as MongoDB offers a solution to the classified advert problem.

b. Describe two use cases for your database in terms of the task a user will want to

perform, the data that will be accessed, and how often the task will be performed.

c. People, objects and classifications are three potential aggregates that could be used

for the database. Explain what this means and compare the advantages and

disadvantages of using each of the suggested aggregates in a classified advert

database. Refer to the use cases you gave in part b. above.

d. MongoDB is a schemaless database. Describe what that means and then discuss

what, if any, restrictions you might choose to impose on documents describing

objects for sale. Say how these restrictions would be imposed and discuss the

advantages and disadvantages of doing so.

e. Thinking about the entries for objects for sale, discuss which fields you would index

on. What would indexing gain for your website and why wouldn’t you simply index

every field?

f. Imagine your database grows very large and is stored over a cluster. You will need to

employ sharding to split the data up. Explain what this means and discuss which

field(s) it would be sensible to use as a shard key. If you now realise that you need a

field that you haven’t mentioned yet, that is fine. Introduce it into the design now

and explain why it is needed.

g. Messages can be sent between two people (usually a buyer and a seller). Discuss

different options for storing messages and linking each message to the people who

sent and received it. Propose three different options and say which of the three you

would choose, and why. In your discussion, refer to particular use cases such as

listing all the messages a person has sent and consider the efficiencies of the

different design choices you propose.

3. Now that you have two designs, one for SQL and one for NoSQL, discuss the relative merits

of each and say which approach you would choose for the database behind a classified

advert website.

Skills: MongoDB, MySQL, SQL

See more: what database does craigslist use, database design for advertisement, craigslist data model, classified website, olx database design, vertabelo, software design website design database, classified ads website design, design database using fifo method sql, design database website hi5, design database website week, classified ads website design couk, ajax php website design database wave, design database using data ware hpusing, website bid design database, design database website facebook, php mysql website design database retail, freelance coder website design database driven website, classified ad website design, database design decision social networking website

About the Employer:
( 0 reviews ) SAINT ETIENNE, France

Project ID: #18898685

6 freelancers are bidding on average $25 for this job


Hi, your project went across my profile, I am doing my bid because I am eligible and taking the initiative to start this project, I am a professional full stack developer. please come over private chat and give me More

$50 USD in 1 day
(14 Reviews)

Hi We are team of fulltime freelancers having 10+ years of experience in website development and application development. We can hell you with developing classified site. Kindly initiate communication to discuss fur More

$15 USD in 45 days
(9 Reviews)

Hi Dear, Our best concern is to follow your requirement and deadline and we will do our best for the success of your project. Feel free to send message to discuss your project with us. Thank you very much! Surely, we More

$15 USD in 1 day
(2 Reviews)

Hi I have reviewed your requirements for your preconceived project As all your requirements are crystal clear to me so i would like to discuss your project querries in more detail which would let me to quote y More

$25 USD in 1 day
(0 Reviews)

Professionally done MySQL, PL/SQL RDBMS. i would like to work with you. If you agree let me know. I currently working with IT Training center.

$20 USD in 5 days
(0 Reviews)

♣ About 7+ years of Professional Experience as a Java Application Developer. ♣ Wide experience using Open Source frameworks and other areas of the Spring Boot, Hibernate, Spring Data JPA, REST, Angular, Service Orient More

$25 USD in 1 day
(0 Reviews)