SQL Mapping

Introduction to SQL Mapping

SQL Mapping is the technique in which we can store the metadata of tables and the attributes stored in the tables in form of columns and the relationship between multiple tables and their attributes in SQL and manipulating it to store it in some other data source or format. We can map the data that is stored inside the SQL databases to objects that are mostly used in application software languages such as C# or Java which are object-oriented and vice versa. In this article, we will study how we can map the tables and relationships present in SQL to the objects and their relationships along with the help of an example.

Property and Relationship mapping:

There are two types of mapping that need to be followed while mapping the SQL database to or from the objects.

The first type of mapping is property mapping which involves mapping the physical attributes and virtual attributes of the objects to one or more columns. It is not always necessary to map all the attributes of objects to their respective columns in tables.

For example, when we have the item name then that attribute should be stored in the table that is why we will create a new column in the table for storing the name of the item. But if there is an attribute for daily sale then that attribute need not be saved in any of the columns of SQL database as it is calculated at the application level. Also, there are situations in which we might need more than one column to store the value of a single attribute of the object in SQL. The property mapping becomes, even more, simpler when the attributes and columns both have the same basic data types. For example, attributes are in string format then columns can be defined in char format or both column and attributes can be in date format, and so on.

The other type of mapping is called relationship mapping where we will be describing how we can persist the relationship that exists between one or more objects and maintaining the same in SQL tables. This is mostly achieved by defining the association, aggregation, and composition relations between the tables and objects. The tables are mapped from the classes.

Example of SQL Mapping

Let us take a simple mapping example where we will take the class model and will see an equivalent physical data model in which each attribute of the class model will be mapped to a single column in the physical data model. Consider an example where we will be storing the class details of the stock at a shop. The following are the attributes that are being defined in the stock class.

Stock Class

purchaseDate: Date
expiryDate: Date
iGST : rupee
sGST : rupee
cGST: rupee
cost: rupee
MRP: rupee
getTotal(): rupee
dispatch()
cancel()
calculateTax()

Orders class (one to many mappings of stock with orders)

getBillAmount()
quantity()

The equivalent mapping of the above two classes of stock and orders into the SQL Database can involve simple mapping with two tables named stock and orders that store the data in the below datatypes that is required to generate the functionality at the application level with the help of the classes written above.

Stock Table

itemId : int (Primary Key)
purchaseDate: Date
expiryDate: Date
iGST : Float
sGST : Float
cGST: Float
cost: Float
MRP: Float
latestUpdation :Timestamp

Orders table

orderId: int (Primary Key)
itemId: int (Primary Key, Foreign Key)
quantity: int
latestUpdation: TimeStamp

Relationship Mapping

In object-oriented platforms, the relationship between different classes can be categorized in two ways. Depending on the multiplicity of the relationship there are three types of relationships that we need to consider while mapping the objects to SQL. These are one to one relationship, one to many also called as many to one relationship and many to many relations.

In one relationship, a maximum of one element is related to one element of the other class or table. For example, a single student can have only one rank in the class. In one to many relations, a maximum of one element is related to many elements of the other class or table. For example, a single student can opt-out for multiple sports and extra curriculum activities. In many to many relations, any number of elements are related to many elements of the other class or table. For example, a single student can be assigned multiple projects while a single project can be assigned to multiple students.

The relationships between the objects can also be classified depending on the directionality. There are two categories of relationship in this case which are unidirectional relations and bidirectional relationships. In case of unidirectional relations, one object is not aware about the other one but the other one is aware of the original one. For example, in case of student object is aware about the rank but rank object need not be necessarily aware about the student object. In case of bidirectional relations both the objects are aware about each other. For example, student object is aware about the teacher while teacher object also knows the students she/he is related to.

In UML diagrams the unidirectional relations are depicted by using the single arrow while bidirectional relations are denoted by a single line. The multiplicity of the objects is denoted by the 1…1, 1..* and *..* for one to one, one to many and many to many respectively. While mapping all these relationships to the tables the same method is followed unless there is any foreign key reference.

Conclusion

It is very easy to map the classes of Object-Oriented Programming of applications to the tables in SQL. We need to map the attributes of the class to the columns depending on the usage. There are situations in which the attributes are derived from the values of the other attributes also called as base attributes. In this case, only base attributes should be stored as the columns of the table while the derived one can be calculated at the application side and there is no need to store then in SQL while mapping. While mapping the relations between the objects we need to be careful only when the referential integrity constraint comes into picture like in case of foreign keys.

Recommended Articles

We hope that this EDUCBA information on “SQL Mapping” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. SQL ORDER BY CASE
2. SQL GROUP BY Month
3. SQL ORDER BY DATE
4. SQL ORDER BY Ascending