CODEWITHSUNDEEP
mysqldatabasedatabase-designdatabase-schema
nick.jw.park
nick.jw.park

Reputation: 199

1M rows, 1 table, few columns vs 300 tables, 3000 rows, few columns vs 300 columns, 3000 rows, 1 table?

I have tried looking around for the best way to go about with this problem, but I could not find any previous examples of such problem.

I am building a hyperlocal based internet shopping mall, and the zone is divided into about 3000 zones. Each zone holds about 300 items. They are similar items but can be varied by little for each zone. I need to get the list of "available items" for each zone.

Insertion speed is not a matter, and it will be chiefly getting the items based on the "zone" value. What would be the most efficient way to set up the DB for such instance?

  1. 1 table with 1M rows such as

    id | zone | item | avail
    1 | 1 | 1 | Y
    2 | 1 | 2 | N
    ...
    1262| 4 | 35 | Y

  2. 300 tables with 3000 rows such as

    table: zone1
    id | item | avail
    1 | 1 | Y
    2 | 2 | N

    table: zone4
    id | item | avail
    ...
    35 | 35 | Y

  3. 1 table with 300 columns (each per item), 3000 rows

    id | zone | item1 | item2 ...
    1 | 1 | Y | N ...
    ...
    4 | 4 | Y | Y ...

Thanks in advance for any help or any leads I could use so that I could make a decision!

Upvotes: 2

Views: 314

Answers (4)

Dennis Anikin
Dennis Anikin

Reputation: 1011

First option is the best one. DBMSs incur big overhead per table and per row. Plus they were not designed for the case of many tables and many rows.

Upvotes: 0

carol
carol

Reputation: 319

Simply use first option. 1M rows, 1 table, few columns .

Upvotes: 0

Cédric Miachon
Cédric Miachon

Reputation: 344

For a Relational Database point of view, you should choose the first option. - If one day you have to add a new item or a new zone, you will not have to create a new column or a new table, and the same if you need to delete a item/zone.

But for a NoSQL point of view, you should choose tables like option 2.

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

On the limit of opinion based, but here we go;

  • Option 1 is most likely what you want.

  • Option 2 would give you 300 tables to maintain, so if you need to add a field later you have 300 tables to alter which sounds like a maintainability nightmare. Also, 300 indexes will most likely cache worse than a single bigger one and searching for a specific item in all zones is basically out of the question.

  • Option 3 would require you to alter the table structure and queries to add more than 300 items. Also, to be able to find an item by id you'd need SQL looking like SELECT xx FROM yy WHERE item1=57 OR item2=57 OR ... OR item300=57 which MySQL's optimizer will most likely just give up on.

Upvotes: 2

Related Questions