Physical data model
Encyclopedia
A physical data model 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 artifacts required to create relationships between tables or achieve performance goals, such as index
es, constraint definitions, linking tables, partitioned tables or cluster
s. 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 disk 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 organization) 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 & 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.
Database management system
A database management system is a software package with computer programs that control the creation, maintenance, and use of a database. It allows organizations to conveniently develop databases for various applications by database administrators and other specialists. A database is an integrated...
. In the lifecycle of a project it is typically derived from a logical data model
Logical data model
A logical data model in systems engineering is a representation of an organization's data, organized in terms of entities and relationships and is independent of any particular data management technology.- Overview :...
, though it may be reverse-engineered from a given database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
implementation. A complete physical data model will include all the database artifacts required to create relationships between tables or achieve performance goals, such as index
Index
Index may refer to:-Business:* Index , a defunct UK catalogue retailer formerly owned by the Littlewoods group and known as Littlewoods Index* INDEX, a market research fair in Lucknow, India* Index fund, a collective investment scheme...
es, constraint definitions, linking tables, partitioned tables or cluster
Cluster
-In science:* Cluster , a small group of atoms or molecules* Cluster chemistry, an array of bound atoms intermediate in character between a molecule and a solid...
s. 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
Informix Dynamic Server
Informix Dynamic Server, also known as IDS, is an extensible Relational Database Management System originally developed by Informix Software Inc....
, Oracle
Oracle Database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
, Postgres
PostgreSQL
PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
, SQL Server
Microsoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
, Sybase
Sybase
Sybase, an SAP company, is an enterprise software and services company offering software to manage, analyze, and mobilize information, using relational databases, analytics and data warehousing solutions and mobile applications development platforms....
, DB2
IBM DB2
The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix , Linux, IBM i , z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions...
and MySQL
MySQL
MySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...
. 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 disk 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 organization) 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 & 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.