Modeling many-to-many relationships without an ORM

Let's say I have a simple database example, something like

create table Items
(ItemId int
,ItemName varchar(50)
,ItemCost decimal
,ItemOrigin varchar(50) --maps to reference of country codes
,primary key (ItemId)
)
go
create table Visits
(VisitId int
,VisitDate datetime
,VisitLocation varchar(50) --maps to references of country codes
,primary key (VisitId)
)
go
create table ItemsVisits
(ItemId int
,VisitId int
,ItemsPurchased int
,ItemExpirationDate datetime
,TotalCost decimal --equals price * items purchased
,primary key(ItemId,VisitId)
)



public class Items
    {
        public int ItemId { get; set; }
        public string ItemName { get; set; }
        public decimal ItemCost { get; set; }
        public string ItemOrigin { get; set; }
    }
    public class Visits
    {
        public int VisitId { get; set; }
        public DateTime VisitDate { get; set; }
        public string VisitLocation { get; set; }
        public List<Items> ItemsPurchased { get; set; }
    }

The two simple classes above were my first inclination. In this example the Items table is sort of a reference table to what can be purchased, and the 'meat' of the application is in the Visits table. With the current setup above, I have no way of knowing about the expiration dates of the items purchased. (Pretend in this scenario that a certain ItemId varies with its expiration date depending on the date on which is was purchased). Should I add a class like

 public class ItemsVisits
    {
        public int ItemId { get; set; }
        public int VisitId { get; set; }
        public int ItemsPurchased { get; set; }
        public DateTime ItemExpirationDate { get; set; }
        public decimal TotalCost { get; set; }

    }

and then make the Visits table have a list of ItemsVisits objects as a property? Or more generally, I suppose, is this a correct way to model a many-to-many relationships when the junction table consists of more than just a copy of the primary keys of both tables? I'll be learning the Entity Framework eventually, but I'd like to be a little closer to the metal while learning.

Answers


Yes, building the ItemVisits class and then providing the one-to-many relationship via a collection is the right approach. One additional item of interest is you'll need a way to get back to Visits and Items from ItemVisits when you query it. That of course is a more concrete way of saying you need an abstract way of getting from entity-to-entity.

One approach might be lazy-loading. So consider a couple new properties on the ItemVisits class:

private Item _baseItem;
public Item BaseItem
{
    get
    {
        if (_baseItem != null) { return _baseItem; }

        // go get the item here and set the internal property
    }
}

The reason you're going to need this is pretty clear. Let's say you're querying ItemVisits and you want to include the Item.ItemName, well you can't actually do that because you don't have an Item.

Another, more old-fashioned approach, would be to pass in the Item and Visit objects when constructing the list of ItemVisit.

Finally, another more scalable approach, would be to leverage caching and an IoC. In this manner you could leverage the constructor of the ItemVisit class and inject those values via an IoC. I would not recommend this unless you have significant transaction load and reason to believe that you would be building these objects at a ridiculous rate. And when I say significant, I'm talking millions of transactions.

EDIT: for multiple primary keys:

If I had a table that was only two primary keys:

public class LinkTable
{
    // exists in the table
    public int ItemId { get; set; }

    private Item _refItem;
    public Item RefItem
    {
        get
        {
            if (_refItem != null) { return _refItem; }

            // go get the item
        }
    }

    // exists in the table
    public int VisitId { get; set; }

    private Visit _refVisit;
    public Visit RefVisit // sample impl as above
}

It's really not any different.


Need Your Help

AVMutableCompositionTrack upside-down when exported

objective-c video rotation avassetwriter avmutablecomposition

I am using a video renderer which captures an openGL layer (irrlicht engine) using gl_readPixels and outputs a video .mp4 file. This takes place is two stages, the first capturing the video and sav...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.