What precautions should I take when creating tables with 1:1 relationship?

Asked

Viewed 100 times

3

Basically I’m in doubt about when I should use 1:1 relationship or a large table.

In my case I would have the object

public class Person(){
  public Guid Id {get;set;}
  public string Name {get;set;}
  public string Cnpj {get;set;}
  public PersonAdress Adress {get;set;}
}

and then I would have the object

public class PersonAdress (){
  public Guid PersonAdressId {get;set;}
  public stirng Enderedo {get;set;}

  public virtual Person Person {get;set;}
}

This is really good practice?

Or would it be better if I had a single entity

public class Person(){
  public Guid Id {get;set;}
  public string Name {get;set;}
  public string Cnpj {get;set;}
  public string Endereço {get;set;}
}

Of course I made a short version of more objects, they would have more fields and more 1:1 relationships.

I did some performance tests on SQLServer and there was a very small difference in a universe of 20,000 records.

But as I know DBA, get overcast.

  • 2

    Yes, it is good practice to separate entities, and your example is right from the point of view of how to relate

  • @Ricardopontual thank you very much!

  • 1

    Yes! That’s good practice, yes. Even, imagine, people move. If you want to keep a history, this way it becomes easier to change further down ne...

1 answer

1


Trying to model smaller tables is undoubtedly a good practice, for this there are levels of database normalization to guide you in this process. Your test does not have results as noticeable because it is a small example (20thousand records for an SQL Server is virtually nothing), the problem will scale along with the size of your project, both in the database and in your back-end due to the ORM.

In the context of the database, normalize serves to decrease the redundancy and duplicity of information within the database. I see many people saying that divide too much causes the need to do a lot of JOINS, but it is worth remembering that Join is one of the basic concepts of the relational bank, being a very well refined and performatic operation.

In the class context, a large object usually stays much longer in memory and survives Garbage Collector inspections, becomes a Gen2 object. The problem with this type of object is that it is necessary to make a Fullgc to clean it, which is quite expensive for performance.

  • thank you very much for your reply, very enlightening, I have a lot of concern with the performance of my applications.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.