We have a large but not huge Database - maybe 400 tables. This is an insurance system. A colleague is suggesting we consolidate all people into one table. This would involve everything from: Users, Witnesses, Claimants, Managers, External Contacts, Drivers, Payees. i.e. everyone.
I support consolidation/generalizing for tables up to a point. We can often make code reusable this way - rather than in the future have to add the same column to several different tables and update all the code (for both Stored Procedures and Applications) that goes along with it. On the other hand, I think this idea of having a single table for all types of peoples is going too far - and may not be practical.
Has anyone worked with a Database that has all types of people stored in a single table? Did this approach work well?
If you were starting from scratch, I would definitely opt for a single table to hold people. Lots going for that approach - for example, gender, age, address, and similar type of information that is common to everyone can then be in one place, and other tables can link to it. I can't think of any negatives to this approach.
Whether you should redesign your existing database to follow that paradigm is a tough call. It depends on how much effort it is.
The data and use there of will determine if you can combine the tables. If there is the same data in multiple tables (i.e. same person) then it might be a good idea. How much code would need changed? What type of column(s) would need added to identify the type?
You are right, my question is vague. I just wondered if anyone has seen something like this in a large system before. I can't image a bank would have all their people-related information in a single table.
As you can imagine a lot of the same data would apply to multiple types of people (i.e. multiple tables in current design). And, many columns will just be relevant for a specific type. Yes, a person can be both a Claimant and a Payee.
We are looking at this firstly if it would be good to do this excluding the effort required to change the current system. As we are considering redoing some of it. Also, if it is not a good idea if we were starting from scratch, then it certainly would not be a viable option to change our current system.
I would imagine that even banks and other high-security requirement establishments would not be worse off by having a single Persons table. There may be regulatory or statutory requirements in some cases to keep the information completely separate for two groups of people - but in that case it is likely that such data is in two different databases, or even likely on two servers which are fire-walled from each other.
What I was suggesting was not to just have only a Persons table. You would have a Persons table which holds information about a person regardless of their relationship with the business (Gender), but then you also would have a Claimant table, and Payee table and other tables as required. If a person was a Claimant and a Payee, they still would have a single entry in the Persons table, but also would have an entry in the Claimant table and the Payee table. The PersonId in the Claimant and Payee tables would be foreign key from the Persons table.
I would support what JamesK said, since you want to make sure that the person in your table(s) is unique (or as close as it can be), otherwise you might end up having a person call Alan Johnson and another call alan johnson which is the same person. not to mention about typo etc.
What you can have is a person table which uniquely identify each person (with all its related info), with another table to state its state (or usage), through simple linking, it might be easier for your app.
Then again, if you redesigning the app, it might need to look at the big picture on the whole thing, eg, project time, effort required, data migration process etc etc.