I used the same tables as in the webshop solution of chapter 9.
However, I added a manufacturer table.

The category table has a 1:M relationship with the products table.
The manufacturer table has a 1:M relationship with the products table.

I need to create a relationship between the category and manufacturer table.

Should I create a table called ManufacturerCategory that connects the category and manufacturer table?

If a Category is produced by more than one manufacturer, then yes. Otherwise, storing the ManufacturerId in the Category would be enough....

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Thank you for your help and fast response Imar. Instant Results is a great book. I also frequent your website and read your articles.
Please keep up the good work.
                   Wayne

You're welcome. Glad you like the book.

Have fun!!

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Imar, I'm dealing with a many to many relationship between category and manufacturer. In my code do I need to create a hybrid class that combines the private fields and public properties of the manufacturer class and category class or is there another way?

example
public class Manufacturer
private int _id;
private string _name;

public class Category
private int _id;
private string description;

public class ManufacturerCategory
private int _categoryID;
private int _manufacturerID;
private string _name;
private string description;

Only problem with this is shouldn't you avoid duplicate code when your able to?

Well, you don't necessarily need to replicate the database behavior in your object model. What about this:

public class Manufacturer
{
  private int _id;
  private string _name;
  private List<Category> _categories;
}

public class Category
{
  private int _id;
  private string description;
  private List<Manufacturer> _manufacturers;
}

This gives each class a generics list of the other, allowing you to do stuff like:

myGridView.DataSource = myCategory.Manufacturers;

or

myManufacturer.Categories.Add(new Category());

This brings some other interesting issues about loading data. E.g. when you load a Manufacturer, do you load all Categories? Probably not, because it means you load all categories, in turn loading all the associated manufacturers for the category, in turn loading all the categories and so on.

For this scenario, you may want to use a concept called lazy loading where you defer loading the data until you actually access it.

With regards to your ManufacturerCategory class: the rules of normalization that apply to databases do not apply to object models. That is, it's OK to have duplicate data in your object model, as long as you don't store it in duplicate fields / tables in the database. With an object model, it's important to normalize behavior, not the underlying data.

Hope this gives you some ideas.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

Once again thanks for the ideas Imar. Your input is greatly appreciated.

Not quite sure how to retrieve data from method in DAL.
CategoryDetails and BrandDetails are BO classes that just contain private fields and public properties. Each BO class has the other BO class as a public generic collection property. I know you said to implement lazy loading but I'm having too much trouble just getting my method to return data from both tables Brand and Category.

The stored procedure works correctly not for certain how I retrieve data from two different tables. CategoryID, Title are in the Category table and BrandID and BrandName are in the Brand table.

public List<CategoryDetails> GetCategoriesByBrand(int brandID)
        {
            List<CategoryDetails> list = new List<CategoryDetails>();

            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("vcs_GetCategoriesByBrandID", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("BrandID", SqlDbType.Int).Value = brandID;
                cn.Open();
                SqlDataReader r = cmd.ExecuteReader();
                CategoryDetails category;
                while (r.Read())
                {
                    category = new CategoryDetails((int)r["CategoryID"],
                      (string)r["Title"]);

                    category.Brands.Add(new BrandDetails((int)r["BrandID"], (string)r["BrandName"]));
                    list.Add(category);
                 }
                cn.Close();
            }
            return list;
        }

Hi there,

And what is the problem? The code you posted could theoretically work, so I don't see any problem with it....

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004