0
\$\begingroup\$

Right now I'm still a beginner in databases and still learning all about it. I have a problem with the design of my database and I don't really know where to go from here. Basically I'm building a music store website that has guitars and bass as products. Now, I have a total of six brands in my database, including its corresponding logo images for displaying in a webpage. Both guitar and bass can use this database for retrieving the needed brand.

brandId  name  image
1         Ibanez    xyz.jpg
2         Fender    abc.jpg
3         Gibson    hjk.jpg
4         PRS        wyx.jpg
5         Musicman cde.jpg
6         ESP        ihl.jpg

Now, I would display those brands for guitar using asp repeater. In this case, the guitar webpage has no problem displaying all the brands because it has all products for that brand. Here is the aspx code for the guitar:

<asp:Content ID='Content1' ContentPlaceHolderID='ContentPlaceHolder1' Runat='Server'>

<asp:Repeater ID="Repeater1" runat="server" OnItemDataBound="repeater_ItemDataBound" ItemType="brand" SelectMethod="GetBrandData"> 
    <ItemTemplate>
        <div class="one-third">
            <div>
             <asp:LinkButton ID="linkButton" OnClick="Repeater1_OnClick" runat="server" CommandArgument='<%# Item.brandId %>'>
                 <asp:Image ID="brandImage" runat="server" height="250px" width="300px" />
             </asp:LinkButton>
            </div>

        </div>
    </ItemTemplate>

And here is the code-behind for the guitar:

 protected void Page_Load(object sender, EventArgs e)
{

}

public void repeater_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    Image img = e.Item.FindControl("brandImage") as Image;
    img.ImageUrl = "../Images/Brands/" + ((brand)e.Item.DataItem).image;
}

public List<brand> GetBrandData()
{
    MusicStoreDBEntities obj = new MusicStoreDBEntities();
    List<brand> list = new List<brand>();
    list = (from g in obj.brands select g).ToList();

    return list;

}

Meanwhile the bass can only display three brands which is Ibanez,Fender and Musicman. This is where the problem starts. It basically has the same aspx code as the guitar but I've made this change to the sql query so that it can return only those three brands.

public List<brand> GetBrandData()
{
    MusicStoreDBEntities obj = new MusicStoreDBEntities();
    List<brand> list = new List<brand>();
    list = (from g in obj.brands where g.name=="Ibanez"||g.name=="Fender"||g.name=="Musicman" select g).ToList();

    return list;

}

I have multiple where clauses in that query so that i can just retrieve those three brands. I really feel like this is not the most efficient way and will cause problems if lets say, i wanted to add a seventh brand and I would need that to display on the bass webpage, then i would need to manually edit query everytime, which is not ideal. I wanted to know if there are other solutions for managing the query or solutions for the design aspect of the database.

I hope you guys can give me some input on this.

\$\endgroup\$
1
\$\begingroup\$

The database should know which as bass

Type 
ID 
name 

1 guitar 
2 bass

Brand 
ID 
name 
image 

where you link the Brand to the Type and a Brand

TypeBrand 
IDtype 
IDbrand 

select Brand.Name 
from Brand 
join TypeBrand 
  on Brand.ID = TypeBrand.IDbrand 
 and TypeBrand.IDtype = 2
\$\endgroup\$
  • \$\begingroup\$ what if the type of lets say brand "Ibanez" is both guitar and bass? How to query? \$\endgroup\$ – RockStar Jul 24 '17 at 14:29
  • \$\begingroup\$ Then it would have two entries in TypeBrand. You would query the same way. \$\endgroup\$ – paparazzo Jul 24 '17 at 14:33
  • \$\begingroup\$ you mean i would query it like this TypeBrand.IDtype = 1 and TypeBrand.IDtype = 2? \$\endgroup\$ – RockStar Jul 24 '17 at 14:49
  • \$\begingroup\$ @RockStar How is "You would query the same way" not clear? You would have two entries (rows) in TypeBrand for the IDbrand with IDtype 1 and 2. \$\endgroup\$ – paparazzo Jul 24 '17 at 14:57
  • \$\begingroup\$ @RockStar No, when you query for all cars that are blue or red you do not look for cars that are blue and red, but for `color = blue OR color = red'. But if you want all types in your case, don't use a WHERE clause. \$\endgroup\$ – oerkelens Jul 25 '17 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.