[indigoimac]

So I am working on a MySQL DB and am trying to figure something out / I don't know what it is called or what to search for to find my answer...

It's a list of dealers, w. a field for their name, address, etc, what I need to add is a field for what brands they sell, which I will call A B and C, now it's obvious how to assign a value of a single brand, but how would I make it so that a dealer could sell both brands A and B, or A and C, etc?

And then how would I search for these values, I can probably figure it out once I can visualize how I would do this in the DB, but for now I am confused.

Thanks for any help!

[patman600]

Well, I can think of a few ways to do this. You could have a column for each brand, with a boolean indicating whether the dealer sells that brand. Pretty easy to do searching, but a pain in the butt to add new brands.

Alternatively you could have a separate table with two columns, dealer and brand, with a row for each dealer/brand pair. This would be a little more complicated and require you to do a join on the search, but is more extensible if you get to have too many brands

[mattyb]

Why don't you have a brands table? You could then have a dealer's id along with a brand id. Easy to add a brand, easy to add a dealer, easy to have a dealer selling several brands.

[Chuckit]

What you want is a many-to-many relationship (that's the Googlable term ). As patman and mattyb said, having a junction table representing the relationship between dealers and the brands they sell is the best way to go about it.

[besson3c]

Yeah, what everybody has said is the way to do it, this is the essence of what relational databases are all about. To do it another way (such as serializing your data and throwing this all into a single field) would go against this design.

[mduell]

In the dealers table you could create a boolean column for each brand.

[Arkham_c]

Originally Posted by mduell 

In the dealers table you could create a boolean column for each brand.

That's not a long-term solution. You don't want to alter your schema every time you add a new brand.

Dealer table has a dealer_id primary key
Brand table has a brand_id primary key
Dealer_Brand table has two columns, dealer_id, brand_id

To add a dealer, put it in the dealer table
To add a brand, put it in the Brand table
To add a brand to a dealer, add to the mapping table

To get all brands for a dealer:

select b.*
from Brand b, Dealer_brand db
where b.brand_id = db.brand_id
and db.dealer_id = 1

That will give you all the brands for dealer with dealer_id 1. Etc.

[Chuckit]

Originally Posted by mduell 

In the dealers table you could create a boolean column for each brand.

Besides what Arkham said, that also doesn't make very good use of a relational database. For example, how do you get a list of all the brands a dealer has? You'd either have a massively overcomplicated SQL query or you'd be moving your relationship logic outside the database. A junction table might seem more complicated, but it's actually easier in the long run.