Database Policy/Management

[Arcane Wizard]

So for a college project we're starting to design the diagrams of a new database.

A problem revolves around a connection between contracts and persons, where both the Contract entity and the Person entity have various subtypes. ie EmployeeContract, Employee, etc. Initially we had these connected to eachother directly with a many to many relation which is acceptable for a conceptual database diagram.

[attachment=1703:attachment]

Some .net programmer who doesn't like databases gave a presentation about software architecture - because he worked on the first .net project in our country years ago or something - said it would be better to make it like this:

[attachment=1702:attachment]

Because if there'd be a new contract type then you'd have to add a table and the management of a company would never allow anyone to add tables to a database because of security reasons. I'm thinking that's pretty much bogus as he wouldn't explain why it's a problem any further and we get to determine database policy anyway. The main goal of the database is to be uniform for currently excisting companies that we're doing the project for as well as for future mergings with other companies, so the policy will contain all sorts of merging rules in which we can detail architecture maintainability or whatever to be just fine for adding a new contract type by table.

First of all I'd like to know if anyone sees any problems with adding tables to a critical company database at all - other than some idiot adding dozens of stupid tables causing the database to become unmanagable. Obviously there's not going to be thousands of contract types.

Secondly, security issues in adding tables through user software with the right priviliges from a thouroughly detailed policy detailing such actions? CREATE TABLE is more dangerous than INSERT? A new table and keeping a single join depth for getting any contract worse than 5 joins just to get a contract signee's name? Or are .net programmers simply insane?

The same applies to Person types, can you imagine needing a whole bunch of joins and subqueries and who knows what else just to get a person's wage?

[AlienCC]

To simplify the answer I agree your solution to the problem is correct while his is not, based soley on the information above.

Front-end programmers are typically horrible at database design. The reason behind it is quite simple. They usually learned programming before they learned anything about databases, and are stuck on the programmers mentality. Breaking things into as small of pieces as possible, and focusing only on little chunks of the problem at a time. With databases, you need to keep the whole big picture in your mind the entire time while designing so as to properly influence every aspect of the design for efficency toward what is needed. Leave it to the front-end programmers and you'll have all sorts of weird things going on like looping queries where none are needed, shoddy table design, etc. I've seen it before, and had to clean up after it on legacy projects. There is a reason why programmers are not DBA's.

[Arcane Wizard]

Thanks for your answer, I presented it to my team along with some more arguments like relational rules that may differ per type and we're all convinced his method is not the way to go.