Classifieds Database Design

database-design

I have always worked with CMSs, but I am trying to get into using frameworks like Laravel and Yii. My main issue is when working with CMSs, I didn't have to think much about the database design since it was done for me. I have my plan drawn out on paper, but I am not really sure where to go from here….

I am trying to develop a Craigslist clone, but a little more specific. I have Googled all over for designs, and this is currently what I have.

However, I want certain categories to have specific fields. Some categories may have fields in common with other categories, but not all categories are the same.

For example:

    - Categories
      - Electronics
        - Title
        - Type
        - Description
        - Year
      - Furniture
        - Title
        - Type
        - Description
        - Brand
      ....

Those are just two examples, but I have a huge list of Categories and the required fields for each category.

My current plan is to load all of these fields into the ad table. What effect will this have on performance? At some point there could be 60 fields attached to the ad table, but only 5-10 may be filled at a time, and the others would be empty/NULL.

What is the best way to go about associating images with ads? I was thinking to just create an assets folder and create subfolders based on the ad id, and upload images to those subfolders of the corresponding ad id. Something like…

    - Public
      - Assets
        - Images
          - 1 (post id = 1)
            - img001.jpg
            - img002.jpg
          ...

What's the best way to set up this kind of database? Would sticking to MySQL be best for this? What if I want some states to have certain categories but not others?

Best Answer

You need to decide if your categories are hierarchical (one parent) or graphical (multiple parents). For example, are "cell phones" in the category "electronics" as well as "communications"?

What kinds of fields might your categories have? Use Table Inheritance to allow categories that are similar but not identical.

Let's say you go with hierarchical categories. It should look like this:

-- hierarchical relationships usually have a single table with a field with a self-referencing foreign key:
create table category (
  id bigserial primary key,
  name text not null unique,
  parent_id bigint null references category(id) --if I'm null then I am a top-level category
);

create table ad (
  id bigserial primary key,
  subject text not null
  ...
);

-- this pattern is called a junction table:
create table ad_category (
  ad_id bigint references ad(id),
  category_id bigint references category(id),
  primary key (ad_id, category_id)
);

I would suggest Postgres over MySql, since it supports WITH queries, which make hierarchical querying easier.

Regarding images, consider storing them in Amazon S3 / Cloudfront. You would associated them with the ad via the HTML in the ad. For example, in your UI, the user would upload an image, get the URL back, and this would get plugged in to an <img> tag.

Though you could also create a junction table similar to the ad_category table, and remove orphan images from S3 on a schedule:

create table ad_picture (
  ad_id bigint not null references ad(id),
  picture_url text not null,
  unique (ad_id, picture_url)
);

Related Solutions

Database design with multiple tables

Making one large table would be a mistake: you'll have a table with lots of NULL values and it would soon evolve into a maintenance nightmare. And about saving the data as XML or JSON -- (slaps your wrist) that's for even considering it.

Every Resulttype should get its own table, each of these tables has a primary key over an ID column and (this is the trick!) all of these IDs are taken from the same sequence!

Then you only need a table with the columns projectid, resultid (and optionally resulttype). Since you will look for the primary key, even searching over all tables can be done fast.

Database Design – Scalable Relational Database Design

So you're going to want to store your images on a fast CDN like Cloudfront. Normally I recommend storing pictures in-database, but not 240M of them :)

I'd avoid storing the whole URL, as it's likely to be repetitive. Just store the file name (a five-char case-sensitive name using a-z 0-9 would give you ~900M combinations. If your CDN is case-insensitive wrt URLs then use 6 chars ).

Are there really going to be 240M unique images, or can they be "shared" among entries? Do you expect every user to fill every entry with the max # of images?

You'd want some tables like this:

create table users (
  user_id serial primary key,
  ...
);

create table entries (
  entry_id serial primary key,
  user_id int not null references users(user_id), --index me
  description text,
  ...
);


create table pictures (
  file_name char(5) primary key,
  entry_id int not null references entries(entry_id) --index me
);

or perhaps:

create table pictures (
  picture_id serial primary key,
  file_name char(5) not null unique,
  entry_id int not null references entries(entry_id) -- index me
);

Use connection pooling!

Best Answer

Related Solutions

Database design with multiple tables

Database Design – Scalable Relational Database Design

Related Question