Physical schema

Physical Data Model Options.

A physical data model (a.k.a. database design) is a representation of a data design which takes into account the facilities and constraints of a given database management system. In the lifecycle of a project it is typically derived from a logical data model, though it may be reverse-engineered from a given database implementation. A complete physical data model will include all the database artefacts required to create relationships between tables or achieve performance goals, such as indexes, constraint definitions, linking tables, partitioned tables or clusters. The physical data model can usually be used to calculate storage estimates and may include specific storage allocation details for a given database system.

At present, there are seven main databases in the business market; Informix, Oracle, Postgres, SQL Server, Sybase, DB2 and MySQL. There are a great many other RDBMS systems out there, but these tend either to be legacy databases or used within academia such as universities or further education colleges. A physical data model on each implementation would be significantly different, not least due to the underlying OS requirements that sit underneath them. Examples would be SQL Server which only run on Microsoft Windows operating systems, while Oracle and MySQL can run on Solaris, Linux and other UNIX-based operating systems as well as Windows.

This means that the disc requirements, security requirements and many other aspects of a physical data model will be influenced entirely by the RDBMS that a database administrator (or their organisation) chooses to use.

Whilst there is increasingly debate surrounding which RDBMS is better within various domains, it was generally accepted that Oracle's architecture is best suited to enterprise and larger implementations, SQL Server better for SME's and MySQL adequate for SME's and small businesses. A useful resource for such debate (which contains useful case studies) can be found at the IT QUEST web site.