StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBest database design
text
Body
copied!<p>We have an investment advisory organisation as a client. They provide reporting on their clients’ investments to them. We’re designing a reporting structure for them, however I’m unsure of the optimum database design that caters for the two structures that their clients have.</p> <p>Structure 1: Client has a retirement fund which invests directly in a combination of mutual funds / unit trust funds. These mutual funds in turn invest in securities such as listed equities.<br> Structure 2: Client has a retirement fund which invests in a combination of internal portfolios (funds) which in turn invest in mutual funds (which invest in securities).</p> <p>It’s very easy to cater for Structure 1 by itself, and also simple to cater for Structure 2 by itself. However some clients will be utilising Structure 1 and others Structure 2, and I don’t want to have to have separate queries and reporting for them. One option I’ve thought of is to design for Structure 2 and if a client is on Structure 1, then include a “dummy” internal portfolio. For example, in the example below, Retirement Fund 1 is structured according to Structure 1 and Retirement Fund 3 is structured according to Structure 2 using a dummy internal portfolio. See views vw_Structure1 and vw_Structure2.</p> <p>I’m looking for the best way of doing this. Any ideas?</p> <p>PS: Seems to be a problem posting the DDL, so will post remainder as new post</p> <pre><code>SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [2-InternalPortfolio]( [InternalPortfolioID] [int] IDENTITY(1,1) NOT NULL, [InternalPortfolioName] [varchar](50) NOT NULL, CONSTRAINT [PK_2-InternalPortfolio] PRIMARY KEY CLUSTERED ( [InternalPortfolioID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [2-InternalPortfolio] ON INSERT [2-InternalPortfolio] ([InternalPortfolioID], [InternalPortfolioName]) VALUES (1, N'High Growth') INSERT [2-InternalPortfolio] ([InternalPortfolioID], [InternalPortfolioName]) VALUES (2, N'Conservative') INSERT [2-InternalPortfolio] ([InternalPortfolioID], [InternalPortfolioName]) VALUES (3, N'Dummy Internal Portfolio') SET IDENTITY_INSERT [2-InternalPortfolio] OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [Security]( [SecurityID] [int] IDENTITY(1,1) NOT NULL, [SecurityName] [varchar](50) NOT NULL, CONSTRAINT [PK_Security] PRIMARY KEY CLUSTERED ( [SecurityID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [Security] ON INSERT [Security] ([SecurityID], [SecurityName]) VALUES (1, N'Company 1') INSERT [Security] ([SecurityID], [SecurityName]) VALUES (2, N'Company 2') INSERT [Security] ([SecurityID], [SecurityName]) VALUES (3, N'Company 3') INSERT [Security] ([SecurityID], [SecurityName]) VALUES (4, N'Company 4') SET IDENTITY_INSERT [Security] OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload