0

I want to design a personal CMS that will have posts with some different (text, images, arrays, etc) data in each post. I want to build a good database design for it and to make it as performance wise as it is reasonably possible.

Broadly speaking, I was thinking of making a Posts table with necessary, relevant data(auto-gen-id, post-id, ...other data, like created-at etc...) and inside of this table, I will include a special attribute that will hold a data of the post as a dynamic array of IDs to a Data table:

[ 57, 43, 36, 93, 384, etc... ]

In this way, I will be able to make a request to the post-id in Posts and then retrieve it's data in Data table with O(1).

Is this correct thinking or am I missing here something? Please, if you think it's not a good design or have another relevant critique, put it in a way so I could at least be able to google it out. It will really help me.

Thanks

nerimi
  • 11
  • Does this answer your question? [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy Jan 12 '20 at 12:32
  • An answer to what is "good design" is a book. Read one. – philipxy Jan 12 '20 at 12:33

1 Answers1

0

I would put the primary key from the posts table on each data record relevant to that post and make sure to index this column.

Edit for clarity: In this way, the data records with primary keys 57, 43, 36, 93 and 384 would all contain a reference (foreign key) to the post with primary key X. You will only need to update one table if a piece of data is added, deleted or modified. Maintaining the array in the posts table is much more complicated and error-prone.

Pan
  • 331
  • 1
  • 7
  • Suppose you have a 10, 000, 000 rows Data table. In your case, as I get it, you will have to go through all of it to retrieve relevant data – nerimi Jan 11 '20 at 17:36
  • No. Add an index to the foreign key column in the data table. This will find the relevant rows quickly with a non-unique index scan. 10M rows is actually not very much. I'm working with several 500M+ tables. You tagged MySQL, so make sure to use InnoDB and not MyISAM as the database engine. Set the inoodb_buffer to an appropriate value , depending on your hardware, in the MySQL configuration file. I don't think that it's likely, but if you later get performance issues (hundreds of millions of rows), add more RAM and increase the buffer size. – Pan Jan 11 '20 at 20:13
  • If your tables get that big, you should also add partitioning to the table definition. Partitioning splits the database into smaller chunks on the HD. The optimization engine will only read the relevant chunks depending on your query. The data should already be in RAM, though, if you've set your innodb_buffer properly. You should probably also add more indexes to your tables, possibly using compound keys. Indexes lead to slower writes but much faster reads. Put them on the columns you search by. – Pan Jan 11 '20 at 20:30
  • I'm not sure what the purpose of your CMS is or how you imagine that it will be used. I believe that a primary key or even a compound index on the data table using 'post_PK' and then 'timestamp' would help you immensely. – Pan Jan 11 '20 at 20:36