Single Inheritance or Polymorphic?

Question

I'm programming a website that allows users to post classified ads with detailed fields for different types of items they are selling. However, I have a question about the best database schema.

The site features many categories (eg. Cars, Computers, Cameras) and each category of ads have their own distinct fields. For example, Cars have attributes such as number of doors, make, model, and horsepower while Computers have attributes such as CPU, RAM, Motherboard Model, etc.

Now since they are all listings, I was thinking of a polymorphic approach, creating a parent LISTINGS table and a different child table for each of the different categories (COMPUTERS, CARS, CAMERAS). Each child table will have a listing_id that will link back to the LISTINGS TABLE. So when a listing is fetched, it would fetch a row from LISTINGS joined by the linked row in the associated child table.

LISTINGS
-listing_id
-user_id
-email_address
-date_created
-description

CARS
-car_id
-listing_id
-make
-model
-num_doors
-horsepower

COMPUTERS
-computer_id
-listing_id
-cpu
-ram
-motherboard_model

Now, is this schema a good design pattern or are there better ways to do this?

I considered single inheritance but quickly brushed off the thought because the table will get too large too quickly, but then another dilemma came to mind - if the user does a global search on all the listings, then that means I will have to query each child table separately. What happens if I have over 100 different categories, wouldn't it be inefficient?

I also thought of another approach where there is a master table (meta table) that defines the fields in each category and a field table that stores the field values of each listing, but would that go against database normalization?

How would sites like Kijiji do it?

phillip · Accepted Answer · 2010-12-10T18:27:44.130

Your database design is fine. No reason to change what you've got. I've seen the search done a few ways. One is to have your search stored procedure join all the tables you need to search across and index the columns to be searched. The second way I've seen it done which worked pretty well was to have a table that is only used for search which gets a copy of whatever fields that need to be searched. Then you would put triggers on those fields and update the search table.

They both have drawbacks but I preferred the first to the second.

EDIT

You need the following tables.

Categories - Id - Description

CategoriesListingsXref - CategoryId - ListingId

With this cross reference model you can join all your listings for a given category during search. Then add a little dynamic sql (because it's easier to understand) and build up your query to include the field(s) you want to search against and call execute on your query.

That's it.

EDIT 2 This seems to be a little bigger discussion that we can fin in these comment boxes. But, anything we would discuss can be understood by reading the following post. http://www.sommarskog.se/dyn-search-2008.html

It is really complete and shows you more than 1 way of doing it with pro's and cons. Good luck.

You need a categories table. You need to have a cross-reference table for categories to listings. Then the user would pick a category during search. You would pass category_id to search_stored_proc and it would join against whichever fields you want to search by via the categories-xref table using the category_id. There are a few details I can't get across here. Maybe I should edit the original answer. But this is the concept you would follow. — phillip, Dec 09 '10 at 20:52
Or you can use dynamic sql and just build up your search query and invoke it. That's an excellent approach as well. see this post for how to guide. http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1 — phillip, Dec 09 '10 at 20:53

score 0 · Answer 2 · answered Dec 09 '10 at 20:48

I think the design you have chosen will be good for the scenario you just described. Though I'm not sure if the sub class tables should have their own ID. Since a CAR is a Listing, it makes sense that the values are from the same "domain".

In the typical classified ads site, the data for an ad is written once and then is basically read-only. You can exploit this and store the data in a second set of tables that are more optimized for searching in just the way you want the users to search. Also, the search problem only really exists for a "general" search. Once the user picks a certain type of ad, you can switch to the sub class tables in order to do more advanced search (RAM > 4gb, cpu = overpowered).

Single Inheritance or Polymorphic?

2 Answers2