Passionate about data

Data and its implications on software design and development.

Schema Design in a Document Database

We are using MongoDB on our project, since mongo is document store, schema design is somewhat different, when you are using traditional RDBMS data stores, one thinks about tables and rows, while using a document database you have to think about the schema in a some what different way. Lets say, we want to save a customer object, when using a RDBMS we would come up with Customer, Address, Phone, Email. They are related to each other as shown below. When doing a document database, the schema design actually does not change much, the Customer document contains an array of Addresses, a one to many relationship. You will not need the FK columns or the Primary Key columns on the child tables, since the child rows are embedded in the parent object. The JSON object below shows how the data would look.

Document representing Customer
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
{
"_id" : ObjectId("4bd8ae97c47016442af4a580"),
"customerid" : 99999,
"name" : "Foo Sushi Inc",
"type" : "Good",
"since" : "12/12/2001",
"addresses" : [{
		"address" : "4821 Big Street",
		"city" : "Stone",			
		"state" : "IL",
		"country" : "USA"
	},
	{	"address" : "1248 Barlow Ln",
		"city" : "Hedgestone",			
		"country" : "UK"
	}		
],
"emails" : [
	{"email" : "foousa@sushi.com"},
	{"email" : "foouk@sushi.com"}
],
"phones" : [
	{"phone" : "773-7777-7777"},
	{"phone" : "020-6666-6666"}
]
}

So Instead of 1 Row for customer, 2 rows for address, phone and email each, you get one Customer document. If you want to query for customers in USA. Using RDBMS you would do

Customer search using SQL
1
2
3
SELECT customer.name FROM customer, address
WHERE customer.customerid = address.customerid
AND address.country="USA"

The same query in mongo would look like

Customer search using mongo client
1
db.customers.find({"addresses.country":"USA"},{"name":true})

where customers is the collection in which we store the customers information.