Classifieds website Database design

Question

I'm developing a website where people can post ads related to animals. There are:

1- Animal Sale & Adoption Ads

2- Animal Mating Ads

I'm having a really difficult time visualizing what my database structure should be for the classifieds section. This is what I've come up with so far:

A user can post an ad. Every ad has an Ad Category (Mating/Sale), and an Ad Type (Offering something/Requesting something).

I created a separate table for animal, which will contain all the animal data, and a separate table for breeds. I did this to make it easier to search by animal or by breed.

The ERD is still in a very preliminary stage but I wanted to know whether this would be efficient in terms of performance and storage.

Thank you in advance! :)

Answer 1

My recommendation is the create a 3rd normal form schema and then profile your app. It's difficult to guess on performance. Other factors like indexes and query optimization will have a great impact. Make sure you're using indexes for all WHERE clauses.

I'd also think about partioning by date. You don't say how you'll deal with historical data. It's not too early to think about how long you'll hang onto ads before moving them out to an archive for reporting. You don't want your performance to slow down over time because the table size has grown.

Now that you've posted your schema, I'd recommend that you remove breed_id from the Ad table. There's no relationship there. An Animal can have one Breed.