Is Star/Snowflake database design good for applications? What is the alternative?
In Option 2 the Status_Code appears as non-key in two tables: Study, Contract. But which of those entities does it belong to? And what are the relative cardinalities of those entities?
Looking at Option 1, the Status_Code belongs to the Contract; there can be multiple Studys for a given Contract.
So if you were to adopt option 2, you give OLTP a headache: anytime there's a change in Status of a Contract, transaction processing needs to replicate that change to all Studys, with an obvious risk of getting out of synch. That's called an update anomaly.
Denormalising the model like Option 2 is legitimate in data warehouses, because we want fast reporting. It is unwise in a transaction processing-oriented data model, because of the processing cost and risks of maintaining data in synch.
So for an application focussing on transaction processing, Option 1 is indicated. And yes, you would create a Join view Study &gt; Contract &gt; Status.