CODEWITHSUNDEEP
performancesql-server-2008t-sqlrelational-databaseforeign-key-relationship
deha
deha

Reputation: 815

DB project - improving performance with relationships

I have two tables, let's call them TableA and TableB. One record in TableA is related to one or more in TableB. But there's also one special record within them in TableB for each record from TableA (for example with lowest ID), and I want to have quick access to that special one. Data from both tables aren't deleted - it's a kind of history rarely cleared. How do that the best in terms of performance?
I thought of:
1) two-way relationship, but it will affect insert performance
2) design next table, with primary key as FK_TableA (for TableA record exactly one is "special") and second column FK_TableB and then create view
3) design next table, with primary key as FK_TableA, FK_TableB, make FK_TableA unique and then create view

I'm open for all other ideas :)

Upvotes: 0

Views: 66

Answers (1)

gbn
gbn

Reputation: 432210

4) I'd consider an indexed view to hide the JOIN and row restriction

This is similar to your options 2 and 3 but the DB engine will maintain it for you. With a new table you'll either compromise data integrity or have to manage the data via triggers

Upvotes: 1

Related Questions