0

I have a database (mysql), and in an in-progress table about 80 columns. I'm thinking of separating this information into other tables, and maintaining a 1:1 relationship. But this can impact development time a bit, create more objects, and so on.

What is the advantage of using multiple tables with a 1:1 relationship, instead of using everything in the same table?

Erico Souza
  • 276
  • 4
  • 18
  • 1
    A 1:1 relationship is useful where you have large amounts of data associated with a given entity but where that data is infrequently required. Most relationships in an RDBMS are 1:n. – Strawberry Nov 08 '17 at 13:13
  • @fancyPants Yes, I heard. But I want to know better if I'm going to win with performance, or other advantages. – Erico Souza Nov 08 '17 at 13:21
  • @Strawberry Yes, in several places, I will not need to list all the data. Of course rarely will I have to list everything. So is it better to create 1: 1 relationships? – Erico Souza Nov 08 '17 at 13:23
  • 1
    Generally, no; this step is only needed as and when it can be demonstrated that queries under the revised structure will perform significantly faster. In simple terms, this probably won't happen until you're into millions of rows. – Strawberry Nov 08 '17 at 13:43

2 Answers2

1

When separating one big table into two different ones with a 1:1 relationships basically helps you to have a better look at your database and the scheme related to it. When you build a relational database, the readability is the big plus of doing so. But yes it will make your query more complex and you may lose some efficiency.

Dimitri Bosteels
  • 381
  • 4
  • 12
  • What about performance? Am I going to have any gain, in separating tables? – Erico Souza Nov 08 '17 at 13:18
  • As I said, you may lose some , but maybe in some case gain some (depends of the data you acquire most of the time, if you always need everything, you will lose performance, if not, you may win some) The big advantage of doing so is still the readability of your database scheme – Dimitri Bosteels Nov 08 '17 at 13:20
  • I see that your comments are always very constructive. What is wrong about what I am saying? But cry if you prefer doing so. – Dimitri Bosteels Nov 08 '17 at 13:23
  • @DimitriBosteels It is, in most cases, not necessary at all. So have another gain, make it lose. OK? – Erico Souza Nov 08 '17 at 13:25
  • @EricoSouza Then you may gain some performance because you will have less data and more relevant data. Your query will gain in complexity on the other hand. It's all about compromise – Dimitri Bosteels Nov 08 '17 at 13:28
  • @fancyPants Do you have something better suggestion? Can you explain me? – Erico Souza Nov 08 '17 at 13:28
  • @fancyPants OK no problems. I'm waiting. Tks – Erico Souza Nov 08 '17 at 13:45
1

When you have more than 80 columns in a table, that doesn't sound like you have correctly normalized it to 3NF or higher. But anyway, assuming you did or have good reasons to not normalize any further, there are only 2 reasons to split this into multiple tables.

Reason 1 (manual entry):

For a table with several columns, to reduce memory requirements for queries that do not use the BLOB column, consider splitting the BLOB column into a separate table and referencing it with a join query when needed.

Reason 2 (manual entry):

You have columns where the values are NULL most of the time but you wish to use indexes on them to speed up certain queries.

Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.

That said, these two reasons are rarely the case and apply to very big tables only.

Readability of your schema (like suggested by Dimitri) shouldn't be one of your concerns. I don't understand anyway, how having a table split into multiple improves on this. In fact I think that it worsens it. (EDIT: It turned out in the comments, that Dimitri meant normalization...)

fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • Basic example where it can help readability : One person with one address, it is more clear to have one address table and one person table even though it is a 1:1 relationship (in this example everyone has an address) Anyway, I gave the information I have been taught which is not exactly what you said but. What I want to say is : You know you can actually be polite and nice when answering? And you don't have to taunt other people? – Dimitri Bosteels Nov 08 '17 at 14:06
  • Yes ok you are right sorry for my bad example. Anyway, it still does not change what I said in the end, being an embittered person won't make you any better – Dimitri Bosteels Nov 08 '17 at 14:19
  • 1
    Okay, you're right. I'm sorry, I had a bad day. Will fix some things here and there...still I think that your answer is too vague and misleading. – fancyPants Nov 08 '17 at 14:55
  • No problem It can happen to anyone ! Have a good end of the day ! – Dimitri Bosteels Nov 08 '17 at 14:57
  • c'mon guys, I know you may be having a bad day. But you're making my day better with these explanations.tks – Erico Souza Nov 08 '17 at 15:16