Multiple entity table design help

Question

Multiple entity table design help

Nelson Casanova

SSCommitted

Points: 1570
More actions
March 17, 2017 at 6:34 am

#341777

I'm facing some issue to work out a database design, its a very small database that stores data of Companies, their board members and shareholders.
Initially I thought "ok, let us have an Individuals table, a Companies table and a Shareholders table"... then I looked at the data and realize Companies can also be shareholders...
So... My original implementation is lacking some help... Here's what's on my notepad atm.
Other ideas: Metadata table? Overkill IMO. Single entity table for Individuals and Companies? Maybe...
Any help is appretiated.
My stack: VS2015 ~ C# ~ MSSQL 2014[/url] ~ DevCraft ~ ReSharper ~ DataGrip ~ Linqer[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

If I was implementing it, I'd do a 3 table system:
Company - which would hold the company name, the board member and the shareholder with FK's on board member and share holder to the other 2 tables
BoardMember - which would hold a list of board members
ShareHolder - which would hold a list of share holders and a boolean column to indicate if it is a company or an individual

I'd also have some identity value that is also a PK on the tables so you have somethign nice to build your FK on. But it does depend on how you will be accessing this data too and how big you expect the tables to get.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

You'll need an abstract that represents any legal entity about which you need to store information. In this case, so far that's company or shareholder.

Legal_Entities ( Legal_Entity_Id, Name, ... )

Companies ( Company_id [FK-->Legal_Entities], <other_data_specific_to_just_this_company> )

Board_Members ( Company_Id, Legal_Entity_Id, ... )

Shareholders ( Company_Id, Legal_Entity_Id, ... )

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".