Multilingual System Design: (3)Data Layer (Stored Procedures)

In this post, I will discuss the logic contained in the data layer. The data layer logic is all in stored procedures and functions.

First, Let us discuss the logic for the Languages table. The following stored procedures may be defined on the languages table:

  • AddLanguage: Add new language to the system. You must take care that this stored procedure not only adds a record in the languages table but it also adds a record for that language into all child tables.
  • DeleteLanguage: Delete language from the system. You need to remove all the records related to that language from child tables. You have to check also if it’s the default language for any user then he/she has to change it.
  • UpdateLanguage: Update the details for a language.
  • GetLanguageDetailsById: Retrieves all the language data using language ID.
  • GetAllLanguages: Retrieves all the languages supported by the system.

Then we can define a group of stored procedures on every multilingual parent-child tables:

  • AddRecord: This stored procedure is defined on the parent child records. It first adds a record in the parent table then adds its child record for a specific language and adds empty child record for each other languages. This stored procedure may use two other stored procedures or functions:
    • AddParentRecord.
    • AddChildRecord.
  • UpdateRecord: Updates a parent record and one of its related child so it needs to take a language parameter. Also, it needs another two stored procedures:
    • UpdateParentRecord.
    • UpdateChildRecord.
  • DeleteRecord: Deletes a record from parent table and all of its related child records. Again, it needs the two stored procedures:
    • DeleteParentRecord.
    • DeleteChildRecord.
  • GetRecoredByLang: Retrieves the parent record and one of its children defined using the language paramenter.
  • GetAllRecoreds: Retrieves the parent record and all of its children.

After developing these procedures for all tables, you need to move to the data access layer. In this layer, you have to make functions that allows you to connect to the database and access your tables and stored procedures. It’s so basic and straight forward so I will skip it.

Next time, I will talk about the business logic.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: