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:
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.
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.
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 15th 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:
Invoices and services
We’ll describe each subject area in the same order they are listed.
Section 1: Contracts
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.
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”.
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_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
contract_type_id– References the “contract_type” dictionary.
provider_id– References the
companytable and defines the company that provides a service to the shopping center.
customer_id– References the
companytable 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_idattribute described below.
billing_frequency_id– References the
billing_frequencydictionary. This value defines when invoices for this contract are generated, i.e. daily, weekly, or monthly. In combination with the
billing_unitsattribute, it tells us when to send an invoice to each customer. For example, if
billing_frequency_idreferences the “monthly” value and the value stored in the
billing_unitsattribute 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
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
address, and all related
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– 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_toattribute) to the final date and set the
activevalue for that shop to “0”.
The last table in this subject area is the
shop_on_contract table. This table contains a UNIQUE pair (
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_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_on_contract tables are almost exactly the same as the ones behind the
shop_on_contract tables. For each service, we’ll store its details and a reference to the related contract.
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
shop_id– The ID of the related
Notice that the
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.
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 (
first_invoice_date as the date of the first invoice and the
date_issued for all invoices afterwards) and the period between two invoices (defined with values stored in
billing_units). The attributes in this table are:
invoice_code– A UNIQUE identifier for each invoice.
contract_id– The ID of the related
issued_by_id– The ID of the
companythat 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
issued_to_id– The ID of the
companythe invoice was issued to.
issued_to– All details of the
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!