3

I am working on a project of classified ads site.

I have 12 main categories, and their items are 142, Like vehicles have cars, scooters, bikes, etc. Mobiles Phones have mobiles, tablets, accessories.

Now should i use few tables and use Json ?

Or should i create separate tables for each item (142 tables) ?

Currently i have decided 142 tables to save myself from relations and joins and heavy coding.

Which way is correct or suggest another one. What are the pros and cons ? I have search a lot but didn't got anything that answers my question.

3
  • Do you need to collect different attributes for a tablet listing compared to a scooter listing? If you don't, then as the fine answers below indicate, it's the value of your categorization that determines whether "Samsung. good shape, $500" is applied to a television or a mobile phone.
    – billinkc
    Commented Jan 26, 2016 at 13:20
  • This question probably belongs on a different site - either SO or programmers.stackexchange; more of a domain modeling question than about DBA. Commented Jan 26, 2016 at 15:48
  • @billinkc Some of the attributes are same but in majority the attributes are different !
    – Gammer
    Commented Jan 26, 2016 at 15:50

5 Answers 5

14

You should definitely (in my opinion) not have 142 tables - it'll be a complete mess to name, index and maintain, and you'll generate yourself a lot of extra work if you some day add another category, if you need to move ads from one category to another, etc.

Storing JSON blobs in the database will kill performance when you're performing searches, so I wouldn't go with that either.

A good relational table design (yes, with joins) is really the best approach when you're using a relational database system. For instance, one table with categories (12 records), joined to one table with items (142 records), joined to a table of ads.

3
  • So i have 12 categories, and 142 their items.... So what should i do ? Roughly tell a solution.... And json is not important ?
    – Gammer
    Commented Jan 26, 2016 at 9:21
  • 2
    @Shafee Daniel just gave you a rough solution, see the last paragraph. JSON is very important, and it is very useful when used right. Commented Jan 26, 2016 at 9:23
  • 142 items = 142 rows in a table (plus a lot of rows in other tables that can be joined to them) Commented Jan 26, 2016 at 15:12
5

I would heavily advise you not make 142 tables, it will be a nightmare. But don't necessarily use JSON, either.

Instead, you should have one table containing categories, and another table containing the items. Link items to their respective categories by using a foreign key column. Your items table could have a category_id column, for example.

Do not store raw JSON in your database, if the data inside could be expressed in your database design instead (unless you have a good reason).

I can guarantee that joins are infinitely easier than making separate tables for each category. Even if you do not know how to use them, it's worth learning about them.

4
  • Buddy great answer ! So i will have a categories table, items table. Now let suppose how will i store scooter and mobiles data in one table ? The fields and input is different.
    – Gammer
    Commented Jan 26, 2016 at 16:01
  • Whilst I like the overall view of this, I'd avoid storing specific categories in the main item table as it will limit the number of categories an item could have. I'd create a link table for item_categories which stores the item_id and the category_id so an item can have multiple categories.
    – gabe3886
    Commented Jan 26, 2016 at 16:11
  • 1
    @gabe3886 that's also a good idea. It depends on if you want an item to be linked to an arbitrary number of categories, or just one.
    – Hugo
    Commented Jan 27, 2016 at 7:39
  • 1
    @HugoZink I mention it because I tend to err on the side of caution and look to "future proof" things where possible, and where practical. Not everything can be future proofed by a single link table, but if that's the only extra bit, it's generally worth the effort
    – gabe3886
    Commented Jan 27, 2016 at 9:07
0

IMO you should think about how you are going to use the data when you design the database. You shouldn't be scared of joins, they are a fundamental part of how a relational database works.

You say items have attributes which can vary with the type of item. In some applications this could be a reason to split the items into different tables but in this particular case I don't think that is the way to go because the attributes aren't really core to the application.

What I would instead do is split the attributes into primary attributes that are present for every item, are stored directly in the items table have special meaning to the code (price, date listed, description etc) and extended attributes which can be created merely by adding database records.

So the way I would struture this is something like.

categories table:
categoryid (primary key, autoincrement)
parentcategoryid (self refferential foreign key, null represents a top-level category)

items table:
itemid (primary key, autoincrement)
categoryid (foreign key referencing categories table)
title
description
datelisted
active
price

attributeentries table:
attributeentryid (primary key, autoincrement)
attributetypeid (foreign key referencing attributetypes table
itemid (foreign key referencing items table)
value (probablly a largeish varchar)

If you want to be able to have an item in multiple categories you may want to get rid of the categoryid in the items table (or maybe use it for a primary category) and create a linking table.

0

You'll need relational tables with joins; having 142 tables for what you're suggesting is not using the relational aspect of RDBMS systems to their fullest potential.

There are a few patterns and practices it will help to learn, and one of them is a many-to-many relationship, which you will probably need. The basics of the schema design can be seen in this wikipedia article.

So, for an example, you could have an item, category, and item_category tables.

The item table has a private key named item_id, and the category table has a private key named category_id. The item_category table then only has two columns in it, item_id and category_id.

In this fashion, you can assign multiple categories to a single item, and have multiple items with the same category, thus many-to-many. If you only added a category_id foreign key column to your items table, you could only assign a single category.

It's helpful afterwards to determine how you'll run your SELECT queries on the data, write and audit them for correctness, and then create views like CREATE VIEW [view_name] AS [sql query].

Use this many-to-many pattern again when you would otherwise want to potentially store multiple foreign keys in a single field.

-1

Table name: Categories
Columns:
category_id number(5) not null - primary key
category_desc varchar(100) not null

Table name: Items
Columns:
item_id number(5) not null - primary key
item_desc varchar(100) not null
item_details varchar(2000) not null
item_contact_phone varchar(20) not null
etc...

Table: item_categories
item_id number(5) not null - this is a foreign key back to the Items table
category_id number(5) not null - this is a Foreign Key back to the Categories table.

Hope this helps!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.