Search code examples
sqlsql-serversqlperformance

SQL - One very long table with several foreign keys VS several short tables

What would be better, performance wise?

One table with several foreign keys and a lot of rows

OR

Several tables with one foreign keys with less data per table.

Example:

I want to create one a table that will contains notes for the user to right, and each note will be related to another table. On one option it will be

Notes(ID, Text, CustomerID, AccountID, UserID, AnotherID) which will contain null values for each unrelated foreign key so:

ID  Text           CustomerID  AccountID  UserID   AnotherID
1   "Call James"   1           null       null     null
2   "Call Havale"  null        2          null     null
3   "Call Shimi"   null        null       4556     null

An other option will be to open separate tables that will be typed foreign key for each: 

AccountNotes(ID, Text, AccountID) 

CustomerNotes(ID, Text, CustomerID)

UserNotes(ID, Text, UserID), etc...

The last option will be to treat it like a many to many relation, although it isn't (the Notes_to_Accounts(NoteID,AccountID) table, for example..).

All the answers i found here were about one table with several columnsthen a lot of small tables with less columns, which is not the problem i'm trying to solve.

Solution

  • A offer third option, but it can be too complicated for simple entity(one text field):

    Notes(Id, Text)
AccountNotes(AccountId, NoteId)
CustomerNotes(CustomerId, NoteId)