Is there an advantage to this type of one-to-one relationship?
 

Hi,

I just would like to ask if there is a benefit regarding this type of relationship between tables. Say I have a table named Student. I usually relate a table (one-to-one) to extend it. What I mean is so as to not put too much columns in Student table (and for organization), I typically relate a one-to-one relationship like this:

[Student.General] ---> [Student.Contact]
|
|--------------> [Student.Parent]
|
|--------------> [Etc.]

I distribute some other fields to other tables. I know this will be slower to query, but is there a performance impact for tables with lots of fields. And I also tend to do this so I can read data easier. What are some guidelines for no. of fields in a table? Thanks!

Re: Is there an advantage to this type of one-to-one relationship?
 

I would say that the number of fields in a table has very little impact on database performance. I don't think you will notice a difference between the query speed of a table with just a few fields and a table with many fields. However, there is a limit on how many fields a table can have. For example, in MS Access, I don't think you can have more than 255 fields in a table. Other database managements systems, such as mySQL, support more fields.

Here is a link to a discussion about this topic:
http://www.codingforums.com/archive/...p/t-48570.html

Re: Is there an advantage to this type of one-to-one relationship?
 

Well I understand that for a normalized DB this would not be much help. Look at this:

One-to-one relations

Normalization will never generate two tables with a one-to-one relationship between them. There is no theoretical reason to separate a single entity like this with some fields in a single record of one table and others in a single record of another table but you might want to split a table into two for practical reasons in order to:

* reduce the number of fields in a table and meet some limit in the programming language.
* store large and rarely-used fields in a separate table so that you do not waste time transferring and processing them during routine operations.
* separate sensitive information from commonly-used fields so that the tables can be stored and backed-up separately.

Since I'm no expert take my comments as such.