If you read my previous post, I was talking about DataLibrary class library. This library contains Entity Data Model which works with eShop database. In fact there are two databases used by the solution:
- eShop database which contains all the tables we are using for the custom functionality (users, shopping carts, payments, lookups) and this database was designed from ground up for eShop solution. I will be writing more below about this one.
- eShopContent database is database Composite C1 is using for placing its data. Tables were automatically generated by Composite C1. Interesting thing about Composite C1 is that data is not placed in SQL database right away after installation but you need to download SqlServerDataProvider package from Composite C1 store (you do this from Composite C1 console). I will not go into depths about this database as you can find more documentation on it online and it is easy to figure out where data is placed. Anyway, it is not important at all, except if you are merging different versions of the solution, then it can quickly become a mess if you are not careful!
Here is the ER diagram of the eShop database:
You can see that tables are placed in schemas - lookup, security, user, store and finance. There is only one table in lookup schema as the website is not a production one and there are not many features done on it except for the security and payment integration. In a production system there are usually multiple lookup tables, sometimes there are several lookup schemas, following other schema naming. For instance we can have user schema and lookupUser schema to contain the lookup tables that will be related to the tables in user schema.
Security schema contains tables that hold data for the security model used. Talking about security, I used Forms authentication with custom forms ticket data (forms ticket is an encrypted cookie saved on the client's browser). For more info about it, read the next posts where I will explain Google Sign In integration with Forms authentication model. You can notice that table security.User doesn't contain a password field as we do not have a password for the user - user is logged in trough Google Sign In and we are provided with a claim - in this case a token in a form of unique characters (a string) that we tie to the user data. Next time user logs on the website using Google Sign In we will check if we have the token value in the security.User table, if we do, we log the user in using Forms authentication. This is called Claim based authentication as we are having an Identity provider (IdP) we trust, in this case the Google, to provide is with claims. Claim can be anything, an email, first name, last name, unique id of some sort. I also use email and first and last name claims to populate data on the enrollment screen after first login. Enrollment is like registration but unlike registration we are not creating user identity, we are just enrolling this identity to our security model where it makes sense for us.
User schema is not that interesting, just storing some additional user information like user contact and address details.
Store schema is actually a main schema as it contains tables that hold items data. All items displayed in the eShop store are contained in groups (store.Group table) and items themselves are in store.Item table. ItemDetail and ItemComment tables contain additional information and comments for the particular item. I must say this is a very primitive store system as it has at least three drawbacks: first is that we do not keep the amount of items at hand anywhere. User can order an item but we don't track if we have it anymore. Second drawback is that we have only one level of grouping. This can be easily resolved by adding another column to store.Group table that would contain parent group id value. If value is null, that group is the parent group. If value is not null, that group is a nested group. That way we can create a tree structure with unlimited nesting capabilities. Third drawback is that there is no discount lookup table and discount percentage is entered directly in the store.Item table and thus making job of updating discounts a bit difficult.
Finance schema contains the shopping cart which is connecting user with items they want to buy (items added to cart) and logs the approved or declined transaction response data we receive from Moneris Api (finance.ShoppingCartPayment table). Moneris Api is a dll that I added to the bin folder of we website, created methods around it to give me ability to pay directly from shopping cart by calling one line of code and passing it credit card information. Moneris is a company based in Canada and they have pretty decent payment solutions and good documentation! I can't stress enough how documentation is important when creating an API that will be used by 3rd party developers and Moneris gives you everything in one PDF file, just read it and code away. There are no stored procedures nor functions in database as I am using Linq to query data and Entity Framework for all CRUD operations. Entity data model contains all these tables and follows schemas by having a separate diagram for every schema containing only tables from that schema.
Security schema contains tables that hold data for the security model used. Talking about security, I used Forms authentication with custom forms ticket data (forms ticket is an encrypted cookie saved on the client's browser). For more info about it, read the next posts where I will explain Google Sign In integration with Forms authentication model. You can notice that table security.User doesn't contain a password field as we do not have a password for the user - user is logged in trough Google Sign In and we are provided with a claim - in this case a token in a form of unique characters (a string) that we tie to the user data. Next time user logs on the website using Google Sign In we will check if we have the token value in the security.User table, if we do, we log the user in using Forms authentication. This is called Claim based authentication as we are having an Identity provider (IdP) we trust, in this case the Google, to provide is with claims. Claim can be anything, an email, first name, last name, unique id of some sort. I also use email and first and last name claims to populate data on the enrollment screen after first login. Enrollment is like registration but unlike registration we are not creating user identity, we are just enrolling this identity to our security model where it makes sense for us.
User schema is not that interesting, just storing some additional user information like user contact and address details.
Store schema is actually a main schema as it contains tables that hold items data. All items displayed in the eShop store are contained in groups (store.Group table) and items themselves are in store.Item table. ItemDetail and ItemComment tables contain additional information and comments for the particular item. I must say this is a very primitive store system as it has at least three drawbacks: first is that we do not keep the amount of items at hand anywhere. User can order an item but we don't track if we have it anymore. Second drawback is that we have only one level of grouping. This can be easily resolved by adding another column to store.Group table that would contain parent group id value. If value is null, that group is the parent group. If value is not null, that group is a nested group. That way we can create a tree structure with unlimited nesting capabilities. Third drawback is that there is no discount lookup table and discount percentage is entered directly in the store.Item table and thus making job of updating discounts a bit difficult.
Finance schema contains the shopping cart which is connecting user with items they want to buy (items added to cart) and logs the approved or declined transaction response data we receive from Moneris Api (finance.ShoppingCartPayment table). Moneris Api is a dll that I added to the bin folder of we website, created methods around it to give me ability to pay directly from shopping cart by calling one line of code and passing it credit card information. Moneris is a company based in Canada and they have pretty decent payment solutions and good documentation! I can't stress enough how documentation is important when creating an API that will be used by 3rd party developers and Moneris gives you everything in one PDF file, just read it and code away. There are no stored procedures nor functions in database as I am using Linq to query data and Entity Framework for all CRUD operations. Entity data model contains all these tables and follows schemas by having a separate diagram for every schema containing only tables from that schema.
Tomorrow, I will go deeper into code with some screenshots and explanations. Mainly how I query data with Linq and how data methods look like. I want to do this asap and get database and data layer out of the way before we can go into security and other business layer features.
There is not much to say about data layer integration with Composite C1, integration starts at the business logic layer. Feel free to download zip archive containing database backup files of both eShop and eShopContent databases and restore them to your test sql server to take a closer look.
Thank you.
I definitely enjoying every little bit of it. It is a great website and nice share. I want to thank you. Good job! You guys do a great blog, and have some great contents. Keep up the good work.
ReplyDeleteeShop codes