CODEWITHSUNDEEP
sqldatabase
Solo
Solo

Reputation: 6957

Does column count matter if I need to request all the data from that 1 row?

TL;DR: If you have 30 different values all pointing to same object id and you need to retrieve them all, does it make any difference if this data is in separate rows or all in same row (many columns)?

Little context:

WordPress has this very funny database setup (IMO):

I think I understand what they are doing - bloggers or theme builders wouldn't want to mess with DBs.

This is my first from start-to-finish project and Im not an expert in databases. So I figured it's best to take example from my old friend WordPress. I use Node.js server, GraphQL and MariaDB database.

Project requires a lot of data per post (individual, can't normalize) and I will eventually have a situation where I have like 200 000 rows in post table and 30 times as much in other, this raises a question:

Why just not to put all 30 values to columns instead of rows?

I've watched few hours of technical tutorials and read blogs but Im still not 100% sure how it works under the hood: If I need all the data below in one request, which "design" should I choose? Is WordPress wrong & they just have to do this for user convenience (2nd has lots of repeating keys)?

I don't much care about storage, I care about super fast reads and also kind of fast writes.

-------------------------------------------------------------------
 id |  post id  |  color  |  shape  |  weight  |  length  |  width
-------------------------------------------------------------------
 1       55        blue      sqare      50        110        110

-------------------------------------
 id |  post_id   |  key  |   value  
-------------------------------------
 1       55        color      blue
 2       55        shape      sqare
 3       55        weight     50
 4       55        length     110
 5       55        width      110

Upvotes: 1

Views: 37

Answers (1)

Walter_Ritzel
Walter_Ritzel

Reputation: 1397

Wordpress did that to promote less database changes as their product evolves. Imagine this situation: If they have to create a new column to correspond to a new attribute, how difficult would be for users, at every change, promote the needed database changes. And in fact, most dynamic databases, used in CMS like Wordpress have this concept to allow for easy changes. Other types of systems also does that.

Upvotes: 1

Related Questions