SQL Database Setup

Question

I'm setting up a database to run practice management software for lawsuits. When adding people associated with the suit, some of them will be repeat parties (eg lawyers for the firm) and some will be one-off parties (witnesses, etc). Looking for input on whether to make 1 "case users" table with values for a user id as well as the rest of the info for the one-off parties, or make 2 tables, one being "case users-firm" with 2 columns for the case and the user id, and another "case users-other" with the one-off party information.

Answer 1

Just one other point to add. For privacy and data protection it is best not to store, encourage your users to store, or even set up a framework for storing, any more personal data than you actually need for your purposes. ( In the UK, think 'Data Protection' law ). So unless you are planning on evolving a kind of legal-profession facebook, I would keep the tables separate on this occasion.

Answer 2

I don't see why you would want to have two tables, especially if they are going to have mostly the same fields. On the other hand, if you want to keep a lot more info for the attorneys than for the witnesses (or the other way around) two tables could be beneficial...

Answer 3

It's pretty common to have a "Persons" table, filled with things common to all people like first name, last name, and a primary key. Then store that key everywhere you might want a person. Who knows? Your lawyer might be a witness. No need to duplicate the entry, when they are in fact the same person.