Physical data model represent the model where it describes how data are stored in computer memory, how they are scattered and ordered in the memory, and how they would be retrieved from memory. Basically physical data model represents the data at data layer or internal layer. It represents each table, their columns and specifications, constraints like primary key, foreign key etc. It basically represents how each tables are built and related to each other in DB.
Above diagram shows how physical data model is designed. It is represented as UML diagram along with table and its columns. Primary key is represented at the top. The relationship between the tables is represented by interconnected arrows from table to table. Above STUDENT table is related to CLASS and SUBJECT is related to CLASS. The above diagram depicts CLASS as the parent table and it has 2 child tables – STUDENT and SUBJECT.
In short we can say a physical data model has
- Tables and its specifications – table names and their columns. Columns are represented along with their datatypes and size. In addition primary key of each table is shown at the top of the column list.
- Foreign keys are used to represent the relationship between the tables. Mapping between the tables are represented using arrows between them.
- Physical data model can have denormalized structure based on the user requirement. The tables might not be in normalized forms.
Physical data model is dependent on the RDBMS i.e.; it varies based on the RDBMS used. This means datatype notation varies depending on the RDBMS. For example, we have different datatypes in SQL server and oracle server. In addition, the representation of physical data model diagram may be different, though it contains same information as described above – some may represent primary key and foreign keys separately at the end of the column list. This data model depends on the user / designer how he specifies the diagram and the RDBMS servers. Below diagram shows different ways of representing a table.
Hence object based data model is based on the real requirement from the user, whereas record based data model is based on the actual relationships and data in DB. The Physical data model is based on the table structure in the DB.