CODEWITHSUNDEEP
sql
l1pton17
l1pton17

Reputation: 454

Column in SQL table with reference to multiple tables

I have a table Page [Id, Name]. And table Element[Id, PageId, ViewId, ViewType, Order], where ViewType is name one of the tables: Portfolio, Fund, Position. And ViewId is id of element in relevant table: PortfolioView, FundView, PositionView.

View tables looks like: [Id, PortfolioId, ShowName, ShowYTD, ShowMTD and other]. When I create an item of Element it means that created item of one of the "view" tables.

So for me it looks like a antipattern.

In short, I wish create a model of Page, which contains a ordered Elements which represent a one of the Views (portfolio, fund or position). Which way is better to do it in sql?

Upvotes: 1

Views: 51

Answers (1)

T.D. Smith
T.D. Smith

Reputation: 994

The way I've seen this done is to update your Element table with a column for each of Portfolio, Fund, and Position. The columns need to be nullable, but they'll be legal foreign keys, so SQL will enforce foreign key correctness for you.

If you want to be as technically correct as possible, you could also do the following:

Page[Id, Name]
Element[Id, PageId, ViewId, ViewType, Order]
Element_Portfolio[ElementId, PortfolioId]
Element_Fund[ElementId, FundId]
Element_Position[ElementId, PositionId]
Portfolio[...]
Fund[...]
Position[...]

This avoid having nullable columns, but requires you to have more tables and therefore more joins when you do a query.

Upvotes: 1

Related Questions