Inside A Shopping Center Data Model

Shopping centers are fun to visit and complicated to run. In this article, we look at a data model capable of helping us manage a shopping center.

We shop at shopping centers (also called shopping malls or just malls), but we also go there to see a movie, eat at a restaurant, or hang out with our friends and drink coffee. Just about every city or large town has a shopping center. They provide many different services and we take them for granted. I’d say that my older readers don’t remember what life was like without the local shopping mall, and the younger ones can’t even imagine it. 😉

Today, I’ll present a simple model that could run one or more shopping centers. Let’s start with the business model.

The Shopping Center Business Model

Before we start the data model, we need to answer some questions about shopping centers:

1. What is a shopping center or mall?

   It is a place where a number of shops are located. The main idea is that visitors will be more motivated to visit a specific location if there is more than one shop there. They can choose between different types of products, but they can also look at other things that they weren’t planning to find.

2. Just shops? What about the other things in a shopping center?

   Usually, you’ll find more than just shops in shopping centers. Multiplex cinemas and café bars are the most famous “additional services” provided in shopping centers. There are often temporary stands, kiosks, or shops, too. These sell all kind of toys for kids and adults, as well as food and maybe some services like getting your nails done. Everything in a shopping mall is geared to entertain visitors, to encourage them to spend a long time in the mall, and to get them to spend as much money as possible.

3. What is the history of the modern shopping center?

   3. What is the history of the modern shopping center? The idea of a shopping center might seem like a newfangled product of capitalism, but it’s not. The concept of the shopping center dates back to the ancient Roman forum: a place where people met and conducted trade. (If you’ve played Age of Empires, you’ll know how important the market was.) One of the first such markets was Trajan’s Market, which was located in Trajan’s Forum. Both were named after the Roman Emperor Trajan, who issued the order to build them.

   Another very old predecessor of the shopping mall is the Grand Bazaar in Istanbul. It was constructed in the 15^th century and still operates today. With more than 4,000 shops, it’s one of the world’s largest covered markets. It’s also the #1 tourist attraction on the planet. If you’ll be travelling near Istanbul, be sure not to miss it.

The Data Model

The data model consists of three main subject areas:

• Contracts
• Shopping centers
• Invoices and services

We’ll describe each subject area in the same order they are listed.

Section 1: Contracts

The Contracts subject area contains records for all companies, contracts, and their related details. We’ll also use this area to store information about the relationship between shopping center management and the companies that rent space and also between management and service companies contracted to do things like cleaning and maintenance.

The contract_type dictionary contains values denoting different contract types. The only attribute in this table, besides the primary key, is the type_name attribute. It can contain only UNIQUE values. We can expect values like “sold”, “lease (1 year)” and “lease (5 years)” in this table.

The second dictionary in this area is the billing_frequency dictionary. This stores all values that define intervals for generating new invoices. Values stored in the UNIQUE “name” attribute could include “daily”, “weekly” or “monthly”.

The company table will keep records for all companies (including our own) that we ever have done business with. We’ll use these values when defining companies that signed a contract with us, companies that issued an invoice to us, and companies we have invoiced. For each company, we’ll store:

• company_code – A UNIQUE code used internally to designate that company.
• company_name – The company name.
• company_address – The company’s physical address.
• contact_person, contact_email, contact_phone, contact_mobile – Self-explanatory contact details for a company. We can expect that we’ll have these values, but we may not have all of them. Therefore, all of these attributes could hold NULL values.
• details – All additional company details, in unstructured format.

Now we’re ready to describe the central table in this subject area: the contract table. Contracts describe a business relationship between two companies. For each contract, we’ll store:

• contract_code – A UNIQUE internal value that defines each contract.
• contract_details – All details for that contract, in unstructured format.
• date_signed – The date when the contract was actually signed.
• date_active_from – The date when this contract becomes active.
• date_active_to – The date when this contract expires. This becomes important when we want to rent space to another company for a set period of time (e.g. one year). In some cases, (such as if we sell the space outright) this attribute will be NULL. In such cases, the contract is valid from date_active_from to indefinitely.
• contract_type_id – References the “contract_type” dictionary.
• provider_id – References the company table and defines the company that provides a service to the shopping center.
• customer_id – References the company table and defines a company that the shopping center provides a service to (such as renting space).
• billing_units – Defines how often we send an invoice, in conjunction with the billing_frequency_id attribute described below.
• billing_frequency_id – References the billing_frequency dictionary. This value defines when invoices for this contract are generated, i.e. daily, weekly, or monthly. In combination with the billing_units attribute, it tells us when to send an invoice to each customer. For example, if billing_frequency_id references the “monthly” value and the value stored in the billing_units attribute is “1”, that means we’ll generate an invoice each month.
• first_invoice_date – The date when the first invoice should be or was generated. This is the start date for all other invoices as well. We can calculate when the second invoice should be generated by using the formula first_invoice_date + 1 billing period. To get the date when the third invoice should be generated we’ll add two billing periods; for the third, three billing periods, and so on.

Section 2: Shopping Centers

The Shopping centers subject area consists of three tables that describe all the shopping centers we run and all the shops in them. It is common for one company to manage several shopping centers, perhaps in different parts of a large city. So the database should be able to accommodate this.

I’ll start with the shopping_center table. It lists all the shopping centers we own and/or run. For each shopping center, we’ll store its’ UNIQUE code, name, address, and all related details.

Within each shopping center, there are one or more shops, restaurants, cafes, or even cinemas. All of these are stored in the shop table, and for the sake of convenience, we’ll call them all “shops”. For each one, we’ll store following information:

• shop_code – A UNIQUE value used to internally designate that shop.
• shop_name – The name used for that shop. We could use the shop type (store, cinema, cafe) combined with the designation or the location of the shop.
• shopping_center_id –The shopping center where the shop is located.
• floor – The level where the shop is located, i.e. Level 2. This is a VARCHAR data type because different malls might use different level designations, e.g. A, B, C instead of 1, 2, 3.
• position – A textual description of the shop’s actual location within the shopping center.
• description – An additional textual description of that store.
• active_from, active_to and active – All relate to the fact that a shop can leave its site within the mall. Of course, shops don’t actually vanish. ?? But we may change the layout of our shopping center or combine several small shop sites into one large site. Or maybe the shop sets up business elsewhere. If any of the above happens, we must set the end date (the active_to attribute) to the final date and set the active value for that shop to “0”.

The last table in this subject area is the shop_on_contract table. This table contains a UNIQUE pair (contract_id – shop_id) and a description related to each record stored in the details attribute. Notice that many shops can be on one contract at the same time, but one shop should have only one active contract at a time. This time period is defined by the date_active_from and date_active_to dates in the contract table. When we insert a new record in the shop_on_contract table, we should check there are no overlapping contracts for that shop.

Section 3: Invoices and Services

The third and last subject area in our model is Invoices and services. This area contains the three tables needed to store services and invoices related with contracts. The remaining four tables, which are actually outside this subject area, are here to show the dependencies between tables.

The ideas behind the service and service_on_contract tables are almost exactly the same as the ones behind the shop and shop_on_contract tables. For each service, we’ll store its details and a reference to the related contract.

The service table is a simple dictionary where we’ll store the UNIQUE names of services that are provided to our company. These could be cleaning services, security services, etc.

All services are related to contracts. They could be one-time services, like repairs or upgrades; they could also be ongoing, like cleaning services. Both cases will require contracts with the companies that provide the service. These relations are stored in the service_on_contract table. The attributes are:

• contract_id – The ID of the related contract.
• service_id – The ID of the related service.
• details – All additional details related with this record, in textual format.
• shopping_center_id – The ID of the related shopping_center.
• shop_id – The ID of the related shop.

Notice that the contract_id - service_id pair doesn’t only hold UNIQUE values. We could have many services of the same type on one contract. For example, we could sign a contract with a cleaning company that provides cleaning services for five shops in our center.

Also, both shopping_center_id and shop_id could contain NULL values. This is because we could sign a contract for the whole center or for just a few shops. Therefore, at least one of these attributes should contain NOT NULL values.

The last table in our model is the invoice table. This is where we’ll store all invoices related to customers and contracts. We will assume that invoices are generated automatically using the date of the previous invoice (contract.first_invoice_date as the date of the first invoice and the invoice.date_issued for all invoices afterwards) and the period between two invoices (defined with values stored in contract.billing_frequency_id and contract.billing_units). The attributes in this table are:

• invoice_code – A UNIQUE identifier for each invoice.
• contract_id – The ID of the related contract.
• issued_by_id – The ID of the company that issued an invoice.
• issued_by – All details related to the company that issued the invoice. These details are stored when the invoice is generated and they remain the same no matter what changes are later made to the company table.
• issued_to_id – The ID of the company the invoice was issued to.
• issued_to – All details of the company invoiced.
• invoice_amount, fee, discount, tax and total_amount – Amounts related to that invoice. These should be self-explanatory.
• invoice_serial – The serial number of the invoice. For each contract, we’ll start with an invoice number of “1”.
• “time_created” – The actual moment when this invoice was generated in our system.
• date_issued – The date when the invoice was issued to the customer.
• date_paid – The date when the invoice was paid. It will contain a NULL value until the payment is posted.
• amount_refunded – An amount refunded to the customer for any reason. If nothing was refunded, this value will be “0”.
• invoice_details – Any additional information we want to store for that invoice.

What Would You Change in This Model?

We’ve analyzed a data model that could be used to run one or more shopping centers. Although this model is not complex, it covers the most commonly desired functionalities. Can you think of any improvements to this model? Do you think we should go into more detail? Please tell us!