Blog

MSSQL, MVC Code-First, Schema Design RSS

1 reply

Last post Aug 14, 2017 09:30 AM by Deepak Panchal

‹ Previous Thread|Next Thread ›

Shortcuts

notary

None

0 Points

1 Post

MSSQL, MVC Code-First, Schema Design

Aug 13, 2017 03:52 AM|notary|LINK

Hi all,

I am using MVC code-first with an MSSQL backend.

I am going to be storing address information.

When an end user goes to create an address for someone in the app, they would enter in the ZIP code.
The City would then populate a drop down based on the ZIP code specified.
When the City is then selected, the State is populated in another drop down.
The County and Time Zone is populated based on the ZIP code entered.

All the ZIP codes, time zones, City, State and Counties are unique in the database.
Relationships are being used.

My question:
I would like to know how I can get all the relevant information for the record based on something unique?

Right now, I am not seeing a viable or best solution for this.

My entities look like so:

ZIPCodeList.cs

using IANotary.GlobalProperties;
using IANotary.Helpers;
using IANotary.Resources;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace IANotary.Models
{
    public class ZIPCodeList : BaseEntity
    {
        public ZIPCodeList()
        {
            this.Cities = new HashSet<CityList>();
        }

        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ZIPCodeID { get; set; }

        [Column(TypeName ="varchar")]
        [DataType(DataType.PostalCode)]
        [OverrideDisplayFormat()]
        [DisplayFormat(DataFormatString = "{0:#####}",
                       ApplyFormatInEditMode = true)]
        [Display(Name = nameof(PropertyNames.ZIPCode),
                 ResourceType = typeof(PropertyNames))]
        [Index(IsUnique = true, IsClustered = false)]
        [MaxLength(5)]
        [MinLength(5)]
        [Required(AllowEmptyStrings = false,
                  ErrorMessageResourceType = typeof(SystemMessages),
                  ErrorMessageResourceName = "InputRequired",
                  ErrorMessage = null)]
        [StringLength(5,
                      ErrorMessageResourceType = typeof(SystemMessages),
                      ErrorMessageResourceName = "StringLengthBetween",
                      ErrorMessage = null, MinimumLength = 5)]
        public string ZIPCode { get; set; }

        [Display(Name = nameof(PropertyNames.CountyName),
                 ResourceType = typeof(PropertyNames))]
        [Required(AllowEmptyStrings = false,
                  ErrorMessageResourceType = typeof(SystemMessages),
                  ErrorMessageResourceName = "InputRequired",
                  ErrorMessage = null)]
        public int CountyID { get; set; }

        [Display(Name = nameof(PropertyNames.TimeZone),
                 ResourceType = typeof(PropertyNames))]
        [Required(AllowEmptyStrings = false,
                  ErrorMessageResourceType = typeof(SystemMessages),
                  ErrorMessageResourceName = "InputRequired",
                  ErrorMessage = null)]
        public int TimeZoneID { get; set; }

        [Display(Name = nameof(PropertyNames.CountyName),
                 ResourceType = typeof(PropertyNames))]
        [Required(AllowEmptyStrings = false,
                  ErrorMessageResourceType = typeof(SystemMessages),
                  ErrorMessageResourceName = "InputRequired",
                  ErrorMessage = null)]
        public virtual CountyList Counties { get; set; }

        [Display(Name = nameof(PropertyNames.CityName),
                 ResourceType = typeof(PropertyNames))]
        [Required(AllowEmptyStrings = false,
                  ErrorMessageResourceType = typeof(SystemMessages),
                  ErrorMessageResourceName = "InputRequired",
                  ErrorMessage = null)]
        public virtual ICollection<CityList> Cities { get; set; }
    }
}

CityList.cs

using IANotary.GlobalProperties;
using IANotary.Resources;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace IANotary.Models
{
    public class CityList : BaseEntity
    {
        public CityList()
        {
            this.States = new HashSet<StateList>();
            this.ZIPCodes = new HashSet<ZIPCodeList>();
        }

        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int CityID { get; set; }

        [Column(TypeName = "varchar")]
        [DataType(DataType.Text)]
        [Display(Name = nameof(PropertyNames.CityName),
                 ResourceType = typeof(PropertyNames))]
        [Index(IsUnique = true,
               IsClustered = false)]
        [MaxLength(25)]
        [MinLength(3)]
        [Required(AllowEmptyStrings = false,
                  ErrorMessageResourceType = typeof(SystemMessages),
                  ErrorMessageResourceName = "InputRequired",
                  ErrorMessage = null)]
        [StringLength(25,
                      ErrorMessageResourceType = typeof(SystemMessages),
                      ErrorMessageResourceName = "StringLengthBetween",
                      ErrorMessage = null,
                      MinimumLength = 3)]
        public string CityName { get; set; }

        [Display(Name = nameof(PropertyNames.State),
                 ResourceType = typeof(PropertyNames))]
        [Required(AllowEmptyStrings = false,
                  ErrorMessageResourceType = typeof(SystemMessages),
                  ErrorMessageResourceName = "InputRequired",
                  ErrorMessage = null)]
        public virtual ICollection<StateList> States { get; set; }

        [Display(Name = nameof(PropertyNames.ZIPCode),
                 ResourceType = typeof(PropertyNames))]
        [Required(AllowEmptyStrings = false,
                  ErrorMessageResourceType = typeof(SystemMessages),
                  ErrorMessageResourceName = "InputRequired",
                  ErrorMessage = null)]
        public virtual ICollection<ZIPCodeList> ZIPCodes { get; set; }
    }
}

StateList.cs

using IANotary.GlobalProperties;
using IANotary.Resources;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace IANotary.Models
{
    public class StateList : BaseEntity
    {
        public StateList()
        {
            this.Cities = new HashSet<CityList>();
        }

        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int StateID { get; set; }

        [Column(TypeName = "varchar")]
        [DataType(DataType.Text)]
        [Display(Name = nameof(PropertyNames.StateName),
                 ResourceType = typeof(PropertyNames))]
        [Index(IsUnique = true,
               IsClustered = false)]
        [MaxLength(20)]
        [MinLength(4)]
        [Required(AllowEmptyStrings = false,
                  ErrorMessageResourceType = typeof(SystemMessages),
                  ErrorMessageResourceName = "InputRequired",
                  ErrorMessage = null)]
        [StringLength(20,
                      ErrorMessageResourceType = typeof(SystemMessages),
                      ErrorMessageResourceName = "StringLengthBetween",
                      ErrorMessage = null,
                      MinimumLength = 4)]
        public string StateName { get; set; }

        [Column(TypeName = "varchar")]
        [DataType(DataType.Text)]
        [Display(Name = nameof(PropertyNames.StateAbbrev),
                 ResourceType = typeof(PropertyNames))]
        [Index(IsUnique = true,
               IsClustered = false)]
        [MaxLength(2)]
        [MinLength(2)]
        [RegularExpression(@"[A-Z][A-Z]",
                           ErrorMessageResourceType = typeof(SystemMessages),
                           ErrorMessageResourceName = "MustContainLettersOnly",
                           ErrorMessage = null)]
        [Required(AllowEmptyStrings = false,
                  ErrorMessageResourceType = typeof(SystemMessages),
                  ErrorMessageResourceName = "InputRequired",
                  ErrorMessage = null)]
        [StringLength(2,
                      ErrorMessageResourceType = typeof(SystemMessages),
                      ErrorMessageResourceName = "StringLengthBetween",
                      ErrorMessage = null,
                      MinimumLength = 2)]
        public string StateAbbrev { get; set; }

        [Display(Name = nameof(PropertyNames.CityName),
                 ResourceType = typeof(PropertyNames))]
        [Required(AllowEmptyStrings = false,
                  ErrorMessageResourceType = typeof(SystemMessages),
                  ErrorMessageResourceName = "InputRequired",
                  ErrorMessage = null)]
        public virtual ICollection<CityList> Cities { get; set; }
    }
}

CountyList.cs

using IANotary.GlobalProperties;
using IANotary.Resources;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace IANotary.Models
{
    public class CountyList : BaseEntity
    {
        public CountyList()
        {
            this.ZIPCodes = new HashSet<ZIPCodeList>();
        }

        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int CountyID { get; set; }

        [Column(TypeName = "varchar")]
        [DataType(DataType.Text)]
        [Display(Name = nameof(PropertyNames.CountyName),
                 ResourceType = typeof(PropertyNames))]
        [Index(IsUnique = true,
               IsClustered = false)]
        [MaxLength(25)]
        [MinLength(3)]
        [Required(AllowEmptyStrings = false,
                  ErrorMessageResourceType = typeof(SystemMessages),
                  ErrorMessageResourceName = "InputRequired",
                  ErrorMessage = null)]
        [StringLength(25,
                      ErrorMessageResourceType = typeof(SystemMessages),
                      ErrorMessageResourceName = "StringLengthBetween",
                      ErrorMessage = null,
                      MinimumLength = 3)]
        public string CountyName { get; set; }

        [Display(Name = nameof(PropertyNames.ZIPCode),
                 ResourceType = typeof(PropertyNames))]
        [Required(AllowEmptyStrings = false,
                  ErrorMessageResourceType = typeof(SystemMessages),
                  ErrorMessageResourceName = "InputRequired",
                  ErrorMessage = null)]
        public virtual ICollection<ZIPCodeList> ZIPCodes { get; set; }
    }
}

- Reply
Deepak Panch...

Participant

1830 Points

628 Posts

Re: MSSQL, MVC Code-First, Schema Design

Aug 14, 2017 09:30 AM|Deepak Panchal|LINK

Hi notary,

you had mentioned that,"I would like to know how I can get all the relevant information for the record based on something unique?"

you can try to add one unique field like 'id' in your table to identify the records uniquely.

then you can try to fire a query in your code to fetch the relevant data.

if you had already developed the code that access the data using query then let us know about that. we can try to check the query in that.

Regards

Deepak