best practices for physical data modelling for SQLserver?

best practices for physical data modelling for SQLserver?

Post by Frieda van der S » Tue, 13 Mar 2001 23:48:50



Does anyone know if there are resources with best practices for
physical data modelling

I'm thinking of
- how many indexes
- valid reasons to denormalize ?
- how many columns in a table ?
- what to think about when defining foreign key constraints ?
- how to implement roles (implement doctors and patients as roles of
"people" because they both have name and address fields ?

Things like that.

I do not expect specific answers from you, but a way to find them

Thanks a lot

Frieda van der Sar

 
 
 

best practices for physical data modelling for SQLserver?

Post by Frank F. Owen II » Thu, 15 Mar 2001 05:34:35


Check out the book Enterprise Data Modeling, available from Amazon at:
http://www.amazon.com/exec/obidos/asin/0471052906/ffoiiicom/105-39277...
325

Physical modeling, like indexing and denormalization (which is justified in
1/10 of the instances it occurrs), depend entirely upon your application and
usage of the data.  Most of the sql server dba books will have at least some
information on indices and their impact.  The general rule of thumb is that
adding indices makes data retrieval faster, but trades off with insert time
and storage space.  Read any book by C.J. Date.

The best education, in my opinion, comes from experience.  This doesn't
necessarily mean doing it as your job, but that helps.  Think of an
application, for example a recipe database.  Then try to normalize it.
Think about the dependencies, think about the impact of table structures.
Think about correlations and where an attribute really resides.

ffoiii



Quote:

> Does anyone know if there are resources with best practices for
> physical data modelling

> I'm thinking of
> - how many indexes
> - valid reasons to denormalize ?
> - how many columns in a table ?
> - what to think about when defining foreign key constraints ?
> - how to implement roles (implement doctors and patients as roles of
> "people" because they both have name and address fields ?

> Things like that.

> I do not expect specific answers from you, but a way to find them

> Thanks a lot

> Frieda van der Sar


 
 
 

best practices for physical data modelling for SQLserver?

Post by Frieda van der S » Thu, 22 Mar 2001 00:44:18


Hi Frank
thanks, I put the book on my list

Frieda

On Tue, 13 Mar 2001 20:34:35 GMT, "Frank F. Owen III"


>Check out the book Enterprise Data Modeling, available from Amazon at:
>http://www.amazon.com/exec/obidos/asin/0471052906/ffoiiicom/105-39277...
>325

 
 
 

1. best practices for a data access layer (DAL) semantics and physical deployment

correct semantics for a data access layer (DAL)

If one looks at the Microsoft examples, one finds two different models for
the bottom logical layer of an n-tiered application. In Duamish, this layer
basically has ADO helper functions that are not associated with any
particular application semantics or data. In FMStocks, the lowest component
layer still has a generic data access layer (DBHelper.cls), but also a DB
layer which has low-level application-specific semantics such as
DecreaseBalance() and GetAccountInfo().

I followed the FMStocks model, but combined by Bus and DB components into my
business logic layer (BLL). But another developer thought a better model
would be to move the FMStocks DB layer down into the DAL, which has the
advantage of the BLL not needing to know anything about the database
implementation. With my approach, the BLL was talking ADO semantics and had
to create the correct queries or use the correct stored procedures - hardy
an abstraction from the database!

The other general question I have is in physical component deployment. In
some of the MS samples, the components are split between the machine(s)
running SQL Server and the machine(s) running IIS. In other samples (and in
my implementation) all components are taken off the database server. Which
is better?

Final question is this. If I am building a high-scalability web site and
have around 8 machines plus a database server, would I get better
performance with all 8 machines being combined IIS/component servers or by
having 4 IIS machines and 4 component server machines? I know that
stress-testing is the only way to know for a particular application, but
time does not always allow testing of every possible permutation and so
general guidelines would be helpful.

2. 3041 can't open a database...

3. Good Modelling Practice?

4. OLAP cannot connect to registry on server

5. IN GOOD FAITH

6. What's a good data modelling package for VFP

7. newbie adp form question

8. Any good books on data modelling?

9. Good books on data modelling?

10. Any good books on data modelling?

11. Dimensional modelling or ER modelling for Data warehouse design

12. best data design practice on performance/size