By using Microsoft Entity Framework, it is advised that developers design the database can support multiple database providers, like Microsoft SQL, MySQL, PostgreSQL, MariaDB or ANY common database engines. The framework has already had an implementation of “Data Provider” layer so that we can program our project with Database Engine independently.
Defining a data table
Recommended that all data tables (Entities) shall place inside the folder “/domain/{modules}”. To declare a table, here is an example:
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace BlueSky.Core.Domain.Common
{
// Rewrittedn by Sammy Cheng, 2022-05-08
/// <summary>
/// Represents a record of Country
/// </summary>
[Table("CountryDef")]
public class Country : BaseCustomKeyEntity, IActiveFilterEntity, ISoftDeletedEntity, ILocalizedEntity
{
/// <summary> Country Code </summary>
[Key]
[Column(TypeName = "varchar(2)")]
public string CountryCode { get; set; }
/// <summary> NopCommerce Mapping Id</summary>
public int? NopCountryId { get; set; }
/// <summary> Country Name </summary>
[Column(TypeName = "nvarchar(100)")]
public string CountryName { get; set; }
/// <summary> Is Allow billing </summary>
public bool? AllowBilling { get; set; }
/// <summary> Is Allow Shipping </summary>
public bool? AllowShipping { get; set; }
/// <summary> ISO Code (3 char) </summary>
[Column(TypeName = "varchar(3)")]
public string IsoCode3 { get; set; }
/// <summary> Num ISO Code </summary>
public int? NumIsoCode { get; set; }
/// <summary> Is VAT </summary>
public bool? IsVAT { get; set; }
/// <summary> Display Order </summary>
public int? DisplayOrder { get; set; }
/// <summary> Implement for IActiveFilterEntity </summary>
public bool Active { get; set; } = true;
/// <summary> Implement for ISoftDeletedEntity </summary>
public bool Deleted { get; set; } = false;
/// <summary> Implement for Localization Service for CustomKeyEntity, to define the CustomKey Value </summary>
[NotMapped]
public override string CustomKey { get { return CountryCode; } }
[ForeignKey("CountryCode")]
public ICollection<Address> Addresses { get; set; }
[ForeignKey("CountryCode")]
public ICollection<StateProvince> StateProvinces { get; set; }
}
}Here is an example to create a class of a data table. Remember to include these library namespace:
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;For these classes, which provide an easy declaration of the data fields types, key and more attributes of all definitions. For most of the developers behavior, they will use an “Identity (Auto Increment)” field “Id” as a primary key. This may be a simply and convenient way to handle the data rows and voiding key violation, but this is not good for big system and not considerable to manage for cross platform data synchronization.
Think about “Country” table below. If we use “Id” as primary key, we have data rows in system A like these:
Id Name DisplayOrder .....
------------------------------------------------------
1 China 0
2 Hong Kong 1
3 Japan 2
4 Korea 3But if you have another “Country” table with these system B:
-----------------------------------------------------
1 China 0
2 Hong Kong 1
3 USA 2
4 Germany 3So, if we want to let both system to be synchronized, or if you are required to post some data from system A to system B. It is terrible to handle the different Id of each value. It is unclear to define the data and increases the issue of data integrity, and it is dangerous to cause system bugs and hard to maintain the data for big database. Therefore, we have some understandings on how to be well-selecting the [Key] fields and type for better data structure.
From my suggestion, I would define the key fields in some rules:
(1) Using character code (Non-Unicode recommended, varchar) for any type code fields
[Key]
[Column(TypeName = "varchar(2)")]
public string CountryCode { get; set; }For these table, I use a two character fields as “CountryCode” and use as primary key, which can be easy to use for foreign keys and manage the data with import and export, export to CSV/Excel and data troubleshooting in any case. The only consideration is when we insert a new record, we may have a concern whether any old record code with the same key code and cause the “Key violent” exception. On the other words, this can avoid duplicate records by mishandling.
However, if we cannot use single key code as primary key, we may use two code fields or three code fields as primary key. Here is an example of “StateProvince” and “County” tables which define 2 and 3 key fields.
// Key fields of StateProvince
[Column(TypeName = "varchar(2)")]
public string CountryCode { get; set; }
[Column(TypeName = "varchar(5)")]
public string StateProvinceCode { get; set; }// 3 key fields for County
[Column(TypeName = "varchar(2)")]
public string CountryCode { get; set; }
[Column(TypeName = "varchar(5)")]
public string StateProvinceCode { get; set; }
[Column(TypeName = "varchar(5)")]
public string CountyCode { get; set; }Why we do like this, it is more applicable for managing a large set of data in most of the cases.
So, you can see, this is advantageous to manage in any cases and it won’t cause any problems during the process of data exchange between different systems.
For the size of the fields, it is not restricted but remember “enough is enough”. What that means? Just consider if the size use as minimal size as you need. Please be careful if you use a big field, it might increase the situation of mishandling of the field data. Normally, varchar(5), varchar(10), varchar(20), varchar(30) is commonly used for most of the cases.
Why we use non-Unicode field for Key? First, all code should be restricted in use of ASCII code and number code, best in capital letters for all the times. Avoiding to use other locales as code naming. The reason of unicode string fields is some systems calculate the string length are different between languages in unicode environment. For a chinese character, if we use non-Unicode fields, one chinese character will count as 2 characters but in unicode fields, some system count as 1 characters and some count as 2. It is a serious bug source if we do some code division calculation or any memory streaming operations.
One more things that Entity Framework (EF) cannot define 2 or 3 fields as primary key simple using [Key] tag, we needed to add code to DataProvider class of model creating like this:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasDefaultSchema("dbo");
modelBuilder.Entity<StateProvince>().HasKey(u => new
{
u.StateProvinceCode,
u.CountryCode
});
modelBuilder.Entity<County>().HasKey(u => new
{
u.CountryCode,
u.StateProvinceCode,
u.CountyCode
});
modelBuilder.Entity<GlobalSubRegion>().HasKey(u => new
{
u.GlobalRegionCode,
u.GlobalSubRegionCode
});
}(2) Using Guid field for Primary Key if the records do not easily to define as code
For example, if you declare a “Client” table or “Vendor” table, user may want to create a code for each record but the identifier is so important to identify the record in unique key. Using Guid is a better choice for developers.
[Table("ClientMaster")]
public partial class Client : BaseGuidEntity, IUpdateTrackingEntity, IStatusDeletedEntity, ILocalizedEntity
{
/// <summary> Organization / Entity Guid </summary>
public Guid EntityGuid { get; set; }
/// <summary> Client Code (for User Customizable) </summary>
[Column(TypeName = "varchar(30)")]
public string ClientCode { get; set; }
/// <summary> Master Guid </summary>
[Column(TypeName = "varchar(30)")]
public Guid? MasterClientGuid { get; set; }
/// <summary> For Nop Commerce Sychronization </summary>
public int? NopClientId { get; set; }
......When using Guid, remember to create NewGuid() when inserting the records. Guid is very good for cross platform data identifying but it cannot use as sorting because it generated randomly. So, when querying data for the table using Guid primary key field, better to add “OrderBy” to handle the data sorting.
(3) Using String Code or Guid field as Primary Key if using in master table (data in multi-table)
For example, if you store an order data, you must required to use several table to store a set of order data. If is recommended to use a string code field or Guid field as primary key. How to consider using string or Guid? If the system can generate a sequential code when creating the order, and the code should be unique. It is recommended that using string code because it is good for sorting with the code. But however, if the order may not be easy to generate an unique string code when creating the order, I suggest to use Guid instead.
(4) Using identity Id fields as Primary Key if it is a sub-table or slave table and the key fields are not so important
For example, a detail table of the order which store the items of the order. Like following example, we will get the order detail data by “Where OrderCode = ‘Order000166’ “, therefore the key is not so important for any cases. And we can also manage the sorting by adding a “DisplayOrder” fields so that user can easy to adjust the sorting of each records without relating with the Id key.
Sometimes, when we use some Type Definition tables but the type is not so important for cross platform. We can use Identity “Id” field as primary key also.
For more information about how to define a Table Class, please refer to Microsoft documents about Entity Framework.
Overview : Developer Guide for BlueSky .NETCORE API Framework
Previous : Using of SVN in Visual Studio as source control (Ch.1)
Next : Using of framework baseClass Inheritance and functional interface (Ch.3)
(c) 2022, BlueSky Information Technology (Int’l) Co. Ltd, All Rights Reserved.