Fluent Nhibernate

Question

I'm very new to NHibernate and DB. I have three tables which are connected with many to many relationship.

Here are the tables creation Script:

create table PATIENT_INFO(patient_Code  int not null identity(1,1) PRIMARY KEY, 
PATIENT_NAME VARCHAR(30), PATIENT_ADRESS VARCHAR(30), PATIENT_BIRTHDAY DATE, 
RECIVED_DATE DATE, ASSIGNED_TO int, FOLLOWUP_DATE DATE, FOLLOWUP_ID INT,
NOTES VARCHAR(750), DATE_INIATED DATE, FOUNDATIONS int, DRUGS VARCHAR(30), NOTIFICATIONS int, GRUNTS int);

CREATE TABLE DOCTORS(DOCTORS_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,DOCTORS_NAME VARCHAR(30),SPETIALTY VARCHAR(30));

CREATE TABLE PATIENT_DOCTOR (PATIENT_ID INT, DOCTOR_ID INT,
CONSTRAINT PATIENT_DOCTOR_PK PRIMARY KEY(PATIENT_ID, DOCTOR_ID),
CONSTRAINT FK_PATIENT FOREIGN KEY(PATIENT_ID) REFERENCES PATIENT_INFO (patient_Code),
CONSTRAINT FK_DOCTOR FOREIGN KEY(DOCTOR_ID) REFERENCES DOCTORS (DOCTORS_ID));

Here are the diagrams for them:

Here are the models made for these three tables:

public partial class PatientDoctor
    {
        public virtual int Id { get; set; }
        public virtual int? PatientID { get; set; }
        public virtual int? DoctorID { get; set; }
        public virtual DoctorEF Doctor { get; set; }
        [IgnoreDataMember]
        public virtual PatientInfoEF PatientInfo { get; set; }
    }

public partial class PatientInfo
    {
        public PatientInfo()
        {
            this.PatientDoctor = new List<Doctor>();
            this.PatientDrugs = new List<Drug>();
        }

        public virtual int PatientCode { get; set; }
        public virtual string PatientName { get; set; }
        public virtual string PatientAdress { get; set; }

        [DataType(DataType.Date, ErrorMessage = "Please enter date")]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        public virtual DateTime? PatientBirthday { get; set; }

        [DataType(DataType.Date, ErrorMessage = "Please enter date")]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        public virtual DateTime? RecivedDate { get; set; }
        public virtual int? AssignedTo { get; set; }
        public virtual int? FollowupId { get; set; }
        public virtual string Notes { get; set; }

        [DataType(DataType.Date, ErrorMessage = "Please enter date")]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        public virtual DateTime? DateIniated { get; set; }
        public virtual int? FoundationId { get; set; }
        public virtual  int? DrugId { get; set; }
        public virtual int? NotificationId { get; set; }
        public virtual int? GrantId { get; set; }
        public virtual string IfOther { get; set; }
        public virtual bool IsInEnrollmentQueue { get; set; }
        public virtual bool IsInGrandTrackerQueue { get; set; }
        public virtual FollowUpItems FollowUpItems { get; set; }
        public virtual Foundation Foundation { get; set; }
        public virtual Grant Grant { get; set; }
        public virtual Notification Notification { get; set; }
        [IgnoreDataMember]
        public virtual IList<Doctor> PatientDoctor { get; set; }
        [IgnoreDataMember]
        public virtual IList<Drug> PatientDrugs { get; set; }
    }

public partial class Doctor
    {
        public Doctor()
        {
            this.PatientDoctor = new List<PatientInfo>();
        }

        public virtual int DoctorsId { get; set; }
        public virtual string DoctorsName { get; set; }
        public virtual string Spetialty { get; set; }
        public virtual IList<PatientInfo> PatientDoctor { get; set; }
    }

Here are the mappings:

class DoctorMap:ClassMap<Doctor>
    {
       public DoctorMap()
        {
            Table("DOCTORS");
            Id(x => x.DoctorsId).Column("DOCTORS_ID");
            Map(x => x.DoctorsName).Column("DOCTORS_NAME");
            Map(x => x.Spetialty).Column("SPETIALTY");
            HasManyToMany(x => x.PatientDoctor)
                .Cascade.All().Inverse()
                .Table("PATIENT_DOCTOR").ParentKeyColumn("DOCTOR_ID").ChildKeyColumn("PATIENT_ID");
        }
    }

    public PatientInfoMap()
            {
                Table("PATIENT_INFO");
                LazyLoad();
                Id(x => x.PatientCode).Column("PATIENT_ID");
                Map(x => x.PatientName).Column("PATIENT_NAME");
                Map(x => x.PatientAdress).Column("PATIENT_ADRESS");
                Map(x => x.PatientBirthday).Column("PATIENT_BIRTHDAY");
                Map(x => x.RecivedDate).Column("RECIVED_DATE");
                Map(x => x.AssignedTo).Column("ASSIGNED_TO");
                //Map(x => x.FollowupId).Column("FOLLOWUP_ID");
                Map(x => x.Notes).Column("NOTES");
                Map(x => x.DateIniated).Column("DATE_INIATED");
                //Map(x => x.FoundationId).Column("FOUNDATION_ID");
                Map(x => x.DrugId).Column("DRUG_ID");
                //Map(x => x.NotificationId).Column("NOTIFICATION_ID");
               // Map(x => x.GrantId).Column("GRANT_ID");
                Map(x => x.IfOther).Column("IF_OTHER");
                Map(x => x.IsInEnrollmentQueue).Column("IS_IN_ENROLLMENT_QUEUE");
                Map(x => x.IsInGrandTrackerQueue).Column("IS_IN_GRAND_TRACKER_QUEUE");

                HasManyToMany(x => x.PatientDoctor)
                    .Cascade.All()
                    .Table("PATIENT_DOCTOR").ParentKeyColumn("PATIENT_ID").LazyLoad();

                HasManyToMany(x => x.PatientDrugs)
                    .Cascade.All()
                    .Table("PATIENT_DRUGS").ParentKeyColumn("PATIENT_ID").LazyLoad();

                References(x => x.FollowUpItems).Column("FOLLOWUP_ID").Index("ID");

                References(x => x.Foundation);

                References(x => x.Grant);

                References(x => x.Notification);
            }
        }

Please tell me if I made my database connection well, and if the mappings and models are good for this database.

Answer 1

There's not much to say about mappings. These things either work or they don't, and since they're posted here I presume they work.

I'd comment on the formatting of the fluent API code, for example in this snippet:

        HasManyToMany(x => x.PatientDoctor)
            .Cascade.All().Inverse()
            .Table("PATIENT_DOCTOR").ParentKeyColumn("DOCTOR_ID").ChildKeyColumn("PATIENT_ID");

It would be more readable to put the chained calls on a new line, like this:

        HasManyToMany(x => x.PatientDoctor)
            .Cascade.All()
            .Inverse()
            .Table("PATIENT_DOCTOR")
            .ParentKeyColumn("DOCTOR_ID")
            .ChildKeyColumn("PATIENT_ID");

Other than that, and this is a personal preference, I find NHibernate mappings clearer in XML configuration files.

---

The T-SQL script that creates the tables isn't quite maintainable; compare this:

create table PATIENT_INFO(patient_Code  int not null identity(1,1) PRIMARY KEY, 
PATIENT_NAME VARCHAR(30), PATIENT_ADRESS VARCHAR(30), PATIENT_BIRTHDAY DATE, 
RECIVED_DATE DATE, ASSIGNED_TO int, FOLLOWUP_DATE DATE, FOLLOWUP_ID INT,
NOTES VARCHAR(750), DATE_INIATED DATE, FOUNDATIONS int, DRUGS VARCHAR(30), NOTIFICATIONS int, GRUNTS int);

To this:

create table PATIENT_INFO(
    patient_Code  int not null identity(1,1) PRIMARY KEY, 
    PATIENT_NAME VARCHAR(30), 
    PATIENT_ADRESS VARCHAR(30), 
    PATIENT_BIRTHDAY DATE, 
    RECIVED_DATE DATE, 
    ASSIGNED_TO int, 
    FOLLOWUP_DATE DATE, 
    FOLLOWUP_ID INT,
    NOTES VARCHAR(750), 
    DATE_INIATED DATE, 
    FOUNDATIONS int, 
    DRUGS VARCHAR(30), 
    NOTIFICATIONS int, 
    GRUNTS int);

I'll add that I find the ALL_CAPS convention very hard on the eyes; I prefer to see T-SQL keywords in all-caps, and identifiers in PascalCase, like this - also I like to explicitly specify the schema a table is to be created in, and I find a PK is easier to spot if it's just called Id, so I'd do it like this:

CREATE TABLE dbo.PatientInfo(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    PatientName VARCHAR(30), 
    PatientAddress VARCHAR(30), 
    PatientBirthday DATE, 
    ReceivedDate DATE, 
    AssignedTo INT, 
    FollowUpDate DATE,
    FollowUpId INT,
    Notes VARCHAR(750), 
    InitiatedDate DATE, 
    Foundations INT, 
    Drugs VARCHAR(30), 
    Notifications INT, 
    Grunts INT);

And since this is a script for a model under development, I'd script it as a drop-and-create.

As for the constraints in the last T-SQL snippet, I prefer scripting them separately from the CREATE TABLE instructions, so a typical script would be structured like this:

/* drop constraints */
IF EXISTS... DROP CONSTRAINT...

/* drop tables */
IF EXISTS... DROP TABLE...

/* create tables */
CREATE TABLE...

/* create constraints */
ALTER TABLE.. ADD CONSTRAINT...