0

I'm designing a schema for a learner management system.

I currently have LearnerDetails table which stores below categories of information. - login user account details - contact details and home address - learner's residency related information including nationality info, current visa details to remain in UK etc - learner's current state benefit related information - details about learner's current employment status

The problem that I have is, when all these information are represented in a single table, number of columns exceed 70 columns.

One thing that I can to do is, I can segregate information in to different tables representing the categories mentioned above and associate these tables to their parent table LearnerDetails as 1:1 relationships.

I'd like to know whether this is a recommended approach or not. In my opinion 1:1 relationships would represent a database what is over normalized. But if I didn't do this, it would result in having a huge horizontal table as my LearnerDetails table.

Highly appreciate if you could let me know your opinions/suggestions.

Eranga Dissanayaka
  • 1,930
  • 3
  • 24
  • 27

3 Answers3

2

There is nothing inherently wrong with many columns in a table, as long you have 5NF, or at least 3NF.

But, there are quite a few examples where vertical partitioning (1::1) makes sense -- take a look at a similar question.

Community
  • 1
  • 1
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
2

How wide are the columns? If your record is wider than the page size then having one wide table is a performance propblem waiting to happen.

Address is generally NOT a 1-1 relationship with person. Yes most people only have one but that is not true of everyone. Students for instcne sometimes live part time with each of their divorced parents. I would suggest that address be separated out. If you store phone numbers, those two are generally not in a 1-1 relationship. You might have a cellophone a fa xnumber a business number and a home phone (landline) number. Anything that hasa good possibility of eventually needing to be in a one-many relationship should be separated out from the start.

If you do separate out the tables and want to enforce the one-to-one relationship, yuo can either use the id from the parent table as the PK inthe child table or have a differnt Pk for the table and set-up a unique index for the FK field. Do not set-up a one-to-one realtionship without a way to enforce it in the database.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
1

There is no problem at all to have 70 or more columns, if that's what normalisation requires. You did not mention which rdbms you use, but most suport at least 255 fields.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • DBMS is SQL Server 2008. Yes it does support the amount of columns that I require. What I basically wanted to know was whether this is a best practice or not. Should I be having multiple smaller tables with related information (1:1 associations with parent table), or should I have a horizontally large table. ( only from the perspective of DB design, without considering any query optimizations related de-normalizations) – Eranga Dissanayaka Aug 30 '11 at 10:57