September 6, 2011 at 9:28 pm
I'm looking to solve a pretty common schema dilemma, I'm just not sure of the best way to go about it.
This is for a legal application - a case can have "x" number of defendants and plaintiffs. A Plaintiff can be a person or a company - likewise for defendants. I'm wrestling with the best way to build and relate these tables. Initially I thought I'd have a PERSON table and a COMPANY table - but then to create the concept of a PLAINTIFF, I want to be able to enforce referential integrity, so doing something like putting an ID field in the PLAINTIFF table that could represent a PERSON or a COMPANY along with a type indicator to specify which it is seems like a bad idea. I'm trying to come up with a way that is logical and will be easy to query. A colleague suggested combining PERSON and COMPANY into one table - thus making it a one to one relationship with a Type field in the PLAINTIFF table to specify if it's a person or company. But that seems wrong too.
These seems like the type of thing that's been handled thousands of times over - but I'm stuck. Any help would be appreciated.
September 7, 2011 at 2:09 am
I don't see why you think having a PLAINTIFF table with a type indicator plus PERSON and COMPANY tables is a bad idea. It seems to me that you have a classic case of a supertype and subtype entities.
Do a google/bing/whatever search for "data model subtype" and you will see plenty of examples.
September 7, 2011 at 3:52 am
Hi,
However there is no harm in having separate column for type and moreever you can have fix length of ID like xxyyyyyyyy where xx can be your type, this is the way you can have both column in he single.
Regards
Ashok
September 7, 2011 at 7:49 am
ashok.faridabad1984 (9/7/2011)
Hi,However there is no harm in having separate column for type and moreever you can have fix length of ID like xxyyyyyyyy where xx can be your type, this is the way you can have both column in he single.
Regards
Ashok
I would strongly advise against using a fixed length field as an ID and more importantly do not use part of a field as some kind of indicator. You will be fighting performance issues the entire length of the system. If you need a piece of data like a type make it a column. Do not make it the first two characters in another column.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply