Help with class definition

Sep 17, 2008 at 6:40 AM
I need help with the class defintion when I use relations.
I have a class User and Room and I must store which user is responsible for which room. So I made a new table with an Id (autoinc with generators), userId and roomID. How do I map this to a correct dbentry class?
Coordinator
Sep 17, 2008 at 8:15 AM
If one person can handle many rooms, it means its a one to many releation. The class should be:

public abstract class User : LinqObjectModel<User>
{
    [ Length(100) ]
    public abstract string Name { get; set; }

    [ HasMany ]
    public abstract IList<Room> Rooms { get; set; }
}

public abstract class Room : LinqObjectModel<Room>
{
    [ Length(100) ]
    public abstract string Name { get; set; }

    [ BelongsTo ]
    public abstract User Owner { get; set; }
}

Please use DbEntry to generate the tables.
Sep 17, 2008 at 11:39 AM
Hello,

I can't generate the tables, they are already present.

Your classes are the same I tried but this doesn't work. I'm getting an error that the coloum USER_ID is not present.

With this way you want add an extra coloum ( USER_ID) to the table Room. Why?

As I said I have those 2 tables and for having the relations I made a 3 table (ID (ID (PK)), UserID (FK to ID of table USER), RoomID FK to ID of table Room), CreatedOn (varchar), CreatedBy(datetime)).

I'm totally confused and go home now. Please upate the wiki and discribe with much more details how relations work in DbEnry.


Coordinator
Sep 17, 2008 at 12:08 PM
If what you want is one to many, the Room table should have the extra coloum User_Id.
If what you want is many to many, there will be a 3rd table with Room_Id and User_Id, but the 3rd table could not be rename or customed in DbEntry. It must follow DbEntry rules. The easiest way to follow the rule is use DbEntry to generate the tables.
If you want extra info in the 3rd table, you can use 2 one-to-many relation to  simulate it. The following code shows it:

public abstract class User : LinqObjectModel<User>
{
    [ Length(100) ]
    public abstract string Name { get; set; }

    [ HasMany ]
    public abstract IList<Xref_User_Room> Xref { get; set; }
}

public abstract class Room : LinqObjectModel<Room>
{
    [ Length(100) ]
    public abstract string Name { get; set; }

    [ HasMany ]
    public abstract IList<Xref_User_Room> Xref { get; set; }
}

public abstract class Xref_User_Room : LinqObjectModel<Xref_User_Room>
{
    [ BelongsTo ]
    public abstract User User { get; set; }

    [ BelongsTo ]
    public abstract Room Room { get; set; }

    [ SpecialName ]
    public abstract DateTime CreatedOn { get; set; }
}

If you don't know what is one to many, many to many, there are alot of articles on internet. It also discussed alot in the documents of NHibernate, ActiveRecored, RoR etc. 

Sep 26, 2008 at 8:28 AM
Hello,

I've omnly used the normalization process to desgin my tables untill now. I took at short look at some documentations about this and I think I want a M:N relation. So I've aleady created the association table between the two tables. How can I force DbEntry to use my table? And why don't you use FKs with constraints when you autocreate the tables?

Coordinator
Sep 26, 2008 at 11:44 AM
You can just rename your 3rd table if your 3rd table don't have extra columns.
If your 3rd table have extra columns, please use 2 one-to-many relation to  simulate it just like I posted before.
With FK, DbEntry need delete the items with some order to avoid the exception from database, it's a little complex so I decide don't use it. So if your table have FK, please delete it.
If your table can't be renamed. You can add the 3rd class, but you need handle the relations by yourself.
Feb 24, 2009 at 3:37 AM
1, How to insert and delete Xref_User_Room?
2, Is there a way to build mutiple column unique index (UserID, RoomID)?
Coordinator
Feb 24, 2009 at 5:42 AM
1.
u.Readers.Add(r1);
u.Save();
u.Readers.Remove(r2);
u.Save();

2.
public abstract class Book : DbObjectModel<Book>
{
    [Index("page_word", UNIQUE=true)]
    public abstract int PageCount;

    [Index("page_word", UNIQUE=true)]
    public abstract int WordCount;
}
Feb 24, 2009 at 6:01 AM
for question 1, is it the same when Xref_User_Room have some propertys rather than SpecialName property,
like, in db

usertable:
id | name

roometable
id | size

user_room_relation
user id | room id | note

is there a way to build object and operate like:

string note = user.Rooms[0].Note;
string size = user.Rooms[0].Size;
...

Coordinator
Feb 24, 2009 at 7:36 AM
Edited Feb 24, 2009 at 7:40 AM
No. such schema should use 2 many:1 like:

public abstract class User
{
   public abstract string Name { get; set; }
   [HasMany] public IList<Xref_User_Room> Xref { get; set; }
}
public abstract class Room
{
   public abstract string Size { get; set; }
   [HasMany] public IList<Xref_User_Room> Xref { get; set; }
}
public abstract class Xref_User_Room
{
    public abstract string Note { get; set; }
    [BelongsTo] public User User { get; set; }
    [BelongsTo] public Room Room { get; set; }
}

string note = user.Xref[0].Note;
string size = user.Xref[0].Room.Size;
Feb 24, 2009 at 8:11 AM
Fabulous, thx!