Database Design — RDBMS
Inaccurate information is probably the most detrimental result of database design as it can adversely affect the organization. In fact, if your database affects the manner in which your business performs its daily operations, or if it’s going to influence the future direction of your business, you must be concerned with database design.
Benefits of Good Design
- The database structure is easy to modify and maintain
- The data is easy to modify
- Information is easy to retrieve
- End user application are easy to develop and build
Developers who have experienced with NoSql Database tend to denormalize the fields when working with SQL, in that regard we will further our discussion on how to deal with fields in SQL database design
An ideal field in SQL have the below properties
- It represents a distinct characteristic of the subject of the table
- It contains only a single value
- It cannot be deconstructed into smaller components
- It does not contain a calculated or concatenated value
- It is unique within the entire database structure
The last point needs further elaboration, the only duplicate fields that appear in a properly designed database are those that establish relationships between tables. If duplicate fields other than these exist in a table, it is very likely that the table will accumulate unnecessary redundant data and that the data within the duplicate fields will inevitably become inconsistent.
Consider the above Manager table, at first glance everything looks fine with the fields of the table but on closer scrutiny we find that there are two fields with multipart value NAME and ADDRESS and BRANCHCODE is a multivalued field
Working with a multipart field is difficult because its value contains two or more distinct items. It’s hard to retrieve information from a multipart field, and it’s hard to sort or group the records in the table by the field’s value. The ADDRESS field in ManagerTable illustrates these difficulties; you would certainly have problems retrieving information for the city of Chennai or sorting information by PIN code.
We resolve a multipart field by identifying the distinct items within the field’s value and treating each item as an individual field.
In ManagerTable, the value of the field NAME represents two items: the first name and the last name of a manager. You resolve this field by creating a new FIRST NAME field and a new LAST NAME field. The value of ADDRESS represents four items: the street address, city, state, and ZIP code of a manager. You transform these items into fields as well; they will appear in the table as STREET ADDRESS, CITY, STATE, and PINCODE.
In the above table we have resolved the problems of Multipart fields but in some case identifying the multipart field might be tricky and careful checking is needed.
Resolving multipart values field is a less hectic job compared to Multivalued field which can be little more difficult to resolve.
A multivalued field has the same fundamental set of problems as a multipart field, as the BRANCHCODE field in ManagerTable above clearly illustrates that. For example, you’ll have difficulty retrieving information for everyone who belongs to a specific branchcode (such as OA), you can’t sort the data in any meaningful fashion, and, most importantly, you don’t have room to enter more than four categories. What happens when one or more RM belongs to five branchcode? The only option you’ll have is to make the field larger every time you need to enter more values than it will currently allow.
So how would we resolve this multivalued field? Our first thought may be to create a new field for each value, thus “flattening” the multivalued field into several single-valued fields.
Next thought is to bring the field into compliance with the second element of an ideal field(‘It contains only a single value’) and declare that it will contain only a single value. Although this is a good impulse and a step in the right direction, it will not resolve the matter completely because it will introduce yet another problem: data redundancy
One possible solution is to remove that field from the table and use it as basis for a new table, In our case we can create a table named MANAGERBRANCHCODE containing ID and BRANCHCODE TYPES
Although the new table contains redundant data, the redundancy is acceptable because it is minimal. Our goal as the database architect is to make certain that it has only an absolute minimum amount of redundant data.
Let us look at another scenario.
ROLE TYPE — This indicates the roles a particular RM belongs too.
ROLE LEVEL — This indicates level of a particular role type.
LANGUAGES SPOKEN — This indicates the languages that an RM can speak.
Our first instinct would be to resolve the multivalued field as we did previously but then we notice one small, relatively obscure problem: There is a distinct one-to-one association between values in ROLETYPE and the values in ROLELEVEL for any given record. We probably wouldn’t have noticed this anomaly had we not carefully examined the sample data within these fields. We will still use the same procedure, but with one minor modification.
Summary:
We have learnt how to resolve the Multipart and Multivalued fields in SQL Database Design, In the next part we will explore the Relationship between tables and scalability issues.
About CodeStax.Ai
At CodeStax.Ai, we stand at the nexus of innovation and enterprise solutions, offering technology partnerships that empower businesses to drive efficiency, innovation, and growth, harnessing the transformative power of no-code platforms and advanced AI integrations.
But the real magic? It’s our tech tribe behind the scenes. If you’ve got a knack for innovation and a passion for redefining the norm, we’ve got the perfect tech playground for you. CodeStax.Ai offers more than a job — it’s a journey into the very heart of what’s next. Join us, and be part of the revolution that’s redefining the enterprise tech landscape.
