1 to zero or one in Orchard

Topics: Writing modules
Aug 16, 2012 at 10:07 PM

Hey All,

Gettin on well with my module, but I have a question. To avoid nulls, I've set up my 1 : 0 or 1 relationships as linked, i.e I have a link table between the entities, and a list on either side.

This ain't optimal, I shouldn't need lists, and I don't want nulls. What are my options here?

Cheers in advance.

Aug 17, 2012 at 11:46 AM

Sorry to bump this, but I need to get this sorted before I go any further..

Aug 17, 2012 at 4:18 PM

I'm not following the question.  Could you provide some code or more detail?

Aug 17, 2012 at 4:31 PM

Sorry yeah, it's a bit vauge.

I need a table that will take an id from another table's ID, in a relationship of 1 : 0 or 1, ie there MAY be a record, in a 1 to 1 relationship.

Aug 17, 2012 at 4:35 PM

I was taught at uni that this was a standard relationship, in the normalization process. Seems to be alot of confusion about this on the internet though, and many people don't seem to know what it's all about. I'm trying to find a good example online, but failing at the moment.. will keep trying

Aug 17, 2012 at 6:39 PM

I'm familiar with the relationships part, just not sure what you're asking still.  Are you having trouble setting this up in Orchard for some reason?  Or, are you just trying to avoid a null property?  

Aug 17, 2012 at 6:43 PM

Thanks for your reply Brandon.

Well, I'd like to know how to set it up, i can avoid nulls as I say using a link table, just wondering if there was a better way.

Aug 17, 2012 at 10:37 PM

All you really need for a 1:1 mapping is two record models and a table for each...

public class Migrations : DataMigrationImpl {
        public int Create() {
            SchemaBuilder
                .CreateTable("ParentRecord",
                             table => table
                                          .Column<int>("Id", column => column.PrimaryKey().Identity())
                                          .Column<string>("Name")
                                          .Column<int>("Child_id"))
                .CreateTable("ChildRecord",
                             table => table
                                          .Column<int>("Id", column => column.PrimaryKey().Identity())
                                          .Column<string>("Name"));                                          
            return 1;
        }

    }

public class ParentRecord
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual ChildRecord Child { get; set; }
    }

    public class ChildRecord {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
    }

Now, for the null part.  I wouldn't bother.  Personally, I like to let the record classes have nulls because I use separate models for views, content, caching, etc.  Those models can be set up to never be null if I want, and I can tap into Orchard's event system to ensure that content parts are initialized the way I want.  No need to do any custom NHibernate mapping.  Just a thought though.  

Aug 17, 2012 at 10:41 PM
Edited Aug 17, 2012 at 10:42 PM

Well if I do this, will it cause problems?

public class Migrations : DataMigrationImpl {
        public int Create() {
            SchemaBuilder
                .CreateTable("ParentRecord",
                             table => table
                                          .Column("Id", column => column.PrimaryKey().Identity())
                                          .Column("Name")
//Change here to unique field
                                          .Column("Child_id", column => column.Unique()))
                .CreateTable("ChildRecord",
                             table => table
                                          .Column("Id", column => column.PrimaryKey().Identity())
                                          .Column("Name"));                                          
            return 1;
        }

    }

public class ParentRecord
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual ChildRecord Child { get; set; }
    }

    public class ChildRecord {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
    }
Aug 17, 2012 at 10:44 PM

And by the way, my database lecturer would probably say that nulls are not an optimum solution, but I understand that it's hard to avoid them in real world scenarios..

Aug 17, 2012 at 10:51 PM

What do you need a unique constraint for?  You're going to have to write your app to handle this scenario anyway.  Not like you can rely on database errors to give the user a nice experience.  

Aug 17, 2012 at 10:54 PM

very true, I'll write into my application also, but if I forget at some point what I'm doing, the database error will remind me ;)

Aug 17, 2012 at 10:56 PM

I think you're better off having a unit test remind you.  The unique constraint is going to force you to not allow nulls which complicates your design quite a bit.  Up to you though.  Good luck!

Aug 17, 2012 at 11:00 PM

Cheers Brandon, I see your point, it's all about how we'd do things, which obviously differs, but that's what makes programming so much fun :D

If the code should not let the database accept nulls, this is gonna tell me for sure that I've forgotten something, that's my way of looking at it, and that's before I get to testing..

Aug 24, 2012 at 5:07 PM

Actually, for anybody reading this, it's gonna confuse them.

The correct way, as I have researched it, is to put the foreign key in the child table, so a parent can exist without a child, and there is no null foreign key in the parent table.

Like so:

SchemaBuilder
                .CreateTable("ParentRecord",
                             table => table
                                          .Column("Id", DbType.Int32, column => column.PrimaryKey().Identity())
                                          .Column("Name", DbType.String))

                                          
                .CreateTable("ChildRecord",
                             table => table
                                          .Column("Id", DbType.Int32, column => column.PrimaryKey().Identity())
                                          .Column("Name", DbType.String)  
                                          .Column("Parent_id", DbType.Int32));

public class ParentRecord
        {
            public virtual int Id { get; set; }
            public virtual string Name { get; set; }

        }

        public class ChildRecord
        {
            public virtual int Id { get; set; }
            public virtual string Name { get; set; }
            public virtual ParentRecord Parent { get; set; }
        }

Aug 25, 2012 at 8:02 PM

Yep, I like that better.