0

Imagine one book table. A book details, are at least 40 items (Like: Name, HeadAuhtor, SecondAuthor, ISBN, DateOfPublish & so many more stupid columns).

I wanna add 30 more columns to this table completely related to my job but not really related to book table (Like: LibraryId, VisitedTimes, DownloadedTimes, HaveFiveStars, HaveFourStars, HaveThreeStars, HaveTwoStars, HaveOneStar [to calculate books rank], SoldTimes, LeasedTimes & some more).

SO, in total we have 70 columns for at least 5 million Books. The second 30 columns will be filled eventually but:

Another important thing is that some libraries may fill All first 40 columns completely but some libraries with many books may fill just 10 of those 40 columns. so in this case we have at least 2 million rows with many NULL or 0 columns.

I want Speed & Performance. This question is very important to me. and I cant test these both way to check the speed and performance myself, so don't tell me go and check it yourself.

I just need one best solution that explains what should I do!

Is it okay if I make a book table with 70 columns? or what? Split 70 columns in 2 tables with 1 to 1 relation? save the first 40 columns as Json in one string field (will Json be fast to get?)?

Does it really matter one 70-column table OR two 40 and 30-column tables with 1:1 relation?

user3824087
  • 11
  • 2
  • does it have to be sql? It sounds like a perfect candidate for a nosql database where properties per object can vary. – JanR Apr 20 '16 at 06:10
  • @JanR , yes it has to be sql, because I have may other tables that are not in this situation. just my book table is a mess. Is it going to hurt with 70 columns or not? I need assurance. – user3824087 Apr 20 '16 at 06:24
  • from experience having that many columns is generally a pain, I would look at the answers below and split it into 2 tables. Another approach could be to store the optional/uncommon fields as a json datatype, https://blogs.msdn.microsoft.com/jocapc/2015/05/16/json-support-in-sql-server-2016/ for more information. – JanR Apr 20 '16 at 06:26
  • also worth noting that you can have a successful hybrid model, between say MSSQL & MongoDB or DocumentDB – JanR Apr 20 '16 at 06:27
  • @JanR, What do you mean having many problem is a pain? in performance is a pain or in coding? because coding is no problem at all. what I worry is performance for 70 columns. and It has to be SQL 2014. – user3824087 Apr 20 '16 at 06:33
  • When having to work with the table, either through objects or manual queries is typically painful, splitting it makes it way more managable – JanR Apr 20 '16 at 06:35

2 Answers2

0

Just add a separate table that has a book_id as a foreign key.

Since not all books will have additional details, left outer join from book table to the additional details table.

tpdi
  • 34,554
  • 11
  • 80
  • 120
  • This join will repeat every time, because I don't know if the user has entered those columns or not. so I have to get them always to use it in my search engine. all of the first 40 one are search able, and the next 30 columns are use able every time some one clicks on a book. – user3824087 Apr 20 '16 at 06:35
  • Yes, the join will repeat every time. SQL databases are very good at joins, especially foreign key joins. – tpdi Apr 21 '16 at 08:53
0

I will create 2 tables where most of the mandatory and important columns in table1(may 10-15 columns),rest in table2.

Most importantly some of your columns are extra,like HaveFiveStars, HaveFourStars, HaveThreeStars, HaveTwoStars, HaveOneStar.So instead of 5 column here you can have only one column like ViewerRating.

Similarly you can eliminate other columns

I think performance will improve . Read this,

Which is more efficient: Multiple MySQL tables or one large table?

Most of the reason are already mention in this link.Also discussion in this link is not abut mySql related rather it is very generic RDBMS related. You should carefully read each and every line.The reson given here are very technical.There is no assumption.

You have mention that there will be 5 million rows.Also most of the column will be null.I will say that not only your performance improve but also it will be very easy to maintain.

There are so many good point mention there in favour of multiple table.

Community
  • 1
  • 1
KumarHarsh
  • 5,046
  • 1
  • 18
  • 22
  • First: when you say 2 tables, are you sure every time I want to get or edit a book I should join? Isn't it bad for this many gets performance? Second: I have to keep track of how many five star rate I have and how many two star I have. – user3824087 Apr 20 '16 at 06:27
  • Still didn't answer my question. that link said some stuff about when to use multi tables but those were not related to my case, (and not even important matters in my idea). tell me this: having 2 tables or 3 tables with 1:1 relation and join to get is better or simply one table with 70 columns? give me technical answer not assumptions. thanks. – user3824087 Apr 20 '16 at 06:51