When you say that your design is "correct", what do you mean?

A design might be "correct" but violate first normal form. Conversely, it might be fully normalized but still fail to model the data accurately.

The "right" approach to your first issue depends on several things. Are there attributes that are rarely used? They might be candidates for placing in one or more side tables. How many attributes do you have?

Will the data be updated frequently ("transaction processing") or will it be mostly queried ("data warehouse") or will the usage be somewhere in the middle?

I would first model the data using only as many entities as necessary. Once you have a clean and clear model that describes the entities and their relationships, you can consider implementation details. Don't get too wrapped up in "optimization", as you may well not know clearly which characteristics you need to optimize for. There is not necessarily a single "correct" solution.

update: so what about the suggestion to use a search engine is so bad that i get 2 downvotes and no reasons told?

update2: in case anybody else got this wrong - normalization *can* lead to worse performance for *searches* and i'm not saying don't normalize.

update 3: "if there will be many searches" doesn't mean that this will influence the speed. it means if there are many searches then it is worthwhile to use a search engine. if you have 3 or 4 damn slow searches a day is not relevant.

rephrased (i thought it was clear what i meant, but apparently this wasn't the case): if you have a database that needs to be normalized but you have many search queries on it that are actually faster than they would be with a normalized data structure then don't hesitate to normalize but use a search engine for the searches.

Howdy!

This is bad advice, taken on its own.

Denormalization is a species of optimization.

Don't blithely do or recommend denormalization without specific data supporting the actual, documentable performance issue. If you don't know the actual issue, you might well simply make things worse.

yours,
Michael

This is bad advice, taken on its own.

Denormalization is a species of optimization.

who recommended denormalization?
i said, *if* you tend to not normalize because of performance issues, use a search engine instead.

Now some people on the team want to split this common table into several tables, and one for each type of item. To me that is absolutely unnecessary

...

I believe my design is correct in both cases, and I also believe those are common issues to lots of monks. But other than knowing what is right, I also need to show evidence that what I am doing is common practice and there are theoretical papers/books/TERMINOLOGIES/web sites behind, otherwise the discussion can go on for quite a while...

So um ... you've decided that your way is best, but you have absolutely no proof of it, so you want someone else to give you evidence of why your way is better?

I'm not sure if this is just complete trolling, or if you're serious. If you're serious, I suggest you give up, and listen to the others on the project. Your solution may functionally work, but it's not going to scale well. I suggest that you come up with the questions that you need to ask of the system, and then both you and the other person come up with solutions -- the other person's will likely be simpler, run faster, and can be more efficiently tuned (less overhead from useless indexing, more compact tables, etc.)

As for references, the best one I have is the Oracle Data Modeling guide, but I don't think it's available unless you take the class.

For your MANAGER and NON_MANAGER example that would be a table EMPLOYEE or PERSON that stores all common data (like address, payment etc) and a MANAGER-table for all data that only appears for managers.