Table Storage: Entity schema can vary in the same table

Question

Hi, while reading about Table storage, it mention that "Entity schema can vary in the same table". I am quite confuse about this statement.

Does it mean that I can have different schema in same table? Can't it be very weird?

Let say I've customer table with the following schema (CustomerId, Name, Phone). Then what does vary mean?

Can I hve another schema (CustomerId, Email, Address)?

Answer 1

Hi Daniell,

You're correct in your understanding, Azure tables are schema less. What that means is that you can have a "Customer" entity and and "Order" entity with different attributes reside in the same Azure table. Let me explain by giving you an example. Let's say your "Customer" entity has these 2 attributes (read columns): CustomerId, Name and your "Order" entity has these 3 attributes: OrderId, CustomerId, OrderTotal.If you were to store these two entities in one table in a relational DB, you would need to create a table with 4 columns: CustomerId, Name, OrderId, and OrderTotal however in Azure Table when you store "Customer" entity, Azure table storage for that entity will only have the attributes applicable to Customer and same goes for "Order" entity as well.

The downside (if you consider it as downside) is that you can't have relationships defined in Azure Tables however on the upside is that one table can store massive amounts of data (in TBs).

Hope this helps.

Thanks

Gaurav Mantri

Cerebrata Software

http://www.cerebrata.com

Answer 2

Hi Gaurav,

Thanks for explaining. I am clear with your explanation until the following sentence

however in Azure Table when you store "Customer" entity, Azure table storage for that entity will only have the attributes applicable to Customer and same goes for "Order" entity as well.

The downside (if you consider it as downside) is that you can't have relationships defined in Azure Tables however on the upside is that one table can store massive amounts of data (in TBs).

• Why should we store 2 different schema entity in 1 table? Why do we mess up into single table with 2 different schema?
• Why wont we just store it in different table -> Customer and Order?

Answer 3

Hi Daniell,

If you wish you could store them in 2 different tables, it's entirely up to you. I think the biggest confusion stems from the fact that it is named "Azure Table Storage". The moment we see the word "table", we imagine a table in a relational database which is not entirely true. A table in Azure Table Storage is more like a property bag where you can put different kinds of things (read entities with different schema) without unnecessarily bloating.

Hope this helps.

Thanks

Gaurav

Answer 4

Hi Gaurav,

Thanks for explaining again. This is true that we always imaging tabular structure when we hear about "table".

But I really don't know what is the reason they (MS) create such a thing like property bag, why dont they just create "classic" table model.

Another thing to be clarified, assuming the scenario from Azure Training Kit, the guestbookdemo. In the GuestBookEntry.cs, we define some properties. Then in the GuestBookDataContext, we create IQueryable GuestBookEntry, I assume that it forms the entire table. Finally in GuestBookDataSource, it encapsulate some method with some datacontext by calling:

CloudTableClient

 

.CreateTablesFromModel(typeof(GuestBookDataContext), storageAccount.TableEndpoint.AbsoluteUri,storageAccount.Credentials);

 

 If it's said that we can create different schema for each row. How it is implemented in the code?

Answer 5

Hi Daniell,

Regarding the 1st question, let me give you an example and see if it makes sense: I use Microsoft Outlook to maintain contacts extensively. Now if you look at the contacts form, you will notice that you could store a lot of information (like Name, Address, Phone, Email, Birthday, Anniversary etc.) about your contacts. I use outlook to store both business and personal contacts. Now for business contacts, I don't really need to store information like birthday, anniversary etc. If I have a schema bound structure then I end up having lots of fields which don't have any value in it i.e. I am not using the storage efficiently. This is where Azure Table Storage schema less nature comes in very handy. I create a table "Contacts" which will store all my contacts. However I will store only the attributes which are needed for a particular contact i.e. for my business contact, I will probably store things like name, company name, designation, email address, phone etc. however for my personal contact, I will store things like name, email address, phone, birthday etc. My business contact entity will not have the information that I don't need and same goes for my personal contact.

Regarding the 2nd question, I've not worked with storage client library so unfortunately I won't be able to comment on that. But I am sure somebody will respond to that (and also add to my explanation above).

Hope this helps.

Thanks

Gaurav

Answer 6

 

It is also a design issue. If you have a very normalized structure, Azure tables do not support transaction the same way as it does in a database normally. So, if you just sending group changes (changes to related tables) to the database one by one, you will need to do using Entity Group Transaction (EGT), otherwise, the  group data may got stuck in some states if some queries fail. In that case you may want to denormalize it for ease of management purpose.

 

---

Thanks, Scott Ma. Schlumberger IT & Software Community Leader/MS SME for Entity Framework and Windows Azure Platform.