SQL table question?

RINO87 · 25-03-2015 1:49pm #1

Hi guys. I'm trying to put together a simple beer locator, along the lines of the beer finder app/map from beior.ie. ( I have e-mailed them to let them know I'm doing this, it's not for commercial gain:))

Im having a little trouble with my database.
I have a table for beers: (ID, brewery, name, type, abv, price, country).
and one for bars:(ID, name, address, lat, lang)

I want each bar to have a selection of beers available, but i'm having trouble doing this. I had initially intended to have a column called "available beers" in the "bars" table, but seeing as each bar will obviously have more than one beer available this won't work as it will break the rule that cells should be atomic.

Im thinking I may have to do a table of bars vs beers?????

25-03-2015 1:56pm

RINO87 wrote: »

Im thinking I may have to do a table of bars vs beers?????

This. It's a many-to-many relationship i.e. one bar can have multiple beers, and one beer can be in multiple bars. So you need a junction table

yes there · 25-03-2015 2:03pm

A really simple way would be to create a table beerLists, put it's beer list ID as a value in your available beers column.Each beer list ID can have multiple beerIDs.

RINO87 · 25-03-2015 2:11pm

Thanks for the input folks!

CruelCoin · 25-03-2015 4:13pm

Have a boolean table maybe?

Bar1
beer1 (true/false), beer2(true/false), etc.

Bar2
beer1 (true/false), beer2(true/false), etc.

And so on.

RINO87 · 25-03-2015 4:34pm

CruelCoin wrote: »

Have a boolean table maybe?

Bar1
beer1 (true/false), beer2(true/false), etc.

Bar2
beer1 (true/false), beer2(true/false), etc.

And so on.

Yeah I had though about going along those lines, however Im hoping to work with 70 beers and 30 bars. It could get messy

yes there · 25-03-2015 4:46pm

You could also just annotate that column with a manyToOne bi directional relationship when you implement it in software when making your entity classes depending on your ORM. I know that would work if you are using Hibernate.

Deliverance XXV · 25-03-2015 10:43pm

Keep it simple and easy to manage.

Beers
ID, brewery, name, type, abv, country

Bars
ID, name, address, lat, lang

Stock
ID, barID, beerID, price

So one bar would have multiple entries of their different beers in the stock table:

Stock Example Data
1, 12, 34, 4.00
2, 12, 14, 4.00
3, 12, 10, 4.00
4, 12, 6, 4.00
5, 12, 21, 4.00
...

This will make queries much easier. You can query for the cheapest beer with the ID 34 in the stock table, etc.

RINO87 · 25-03-2015 11:37pm

That's perfect, simple really when you see it but the ideas still don't come to me immediately. Thanks folks.

amen · 26-03-2015 2:23pm

+1 Deliverance XXV but you also need the following tables (normalise)
Brewery
BreweryID, Name,TypeID,abvID, CountryId

BeerType
TypeID, Beer

ABV
ABVid,ABV

Country
CountryID, Country

Beers
ID, BreweryID, name, typeID, abvID, countryID

ohnonotgmail · 26-03-2015 2:49pm

amen wrote: »

+1 Deliverance XXV but you also need the following tables (normalise)
Brewery
BreweryID, Name,TypeID,abvID, CountryId

BeerType
TypeID, Beer

ABV
ABVid,ABV

Country
CountryID, Country

Beers
ID, BreweryID, name, typeID, abvID, countryID

why would you need a table for the ABV? Surely thats just a number?

The Corinthian · 26-03-2015 5:04pm

CruelCoin wrote: »

Have a boolean table maybe?

Bar1
beer1 (true/false), beer2(true/false), etc.

Bar2
beer1 (true/false), beer2(true/false), etc.

And so on.

You go sit in the corner for that.

ohnonotgmail · 26-03-2015 5:06pm

The Corinthian wrote: »

You go sit in the corner for that.

I've seen worse.

Aswerty · 26-03-2015 5:36pm

ohnonotgmail wrote: »

I've seen worse.

Bar1
beer1 ('yes'/'no'), beer2('yes'/'no'), etc.

Bar2
beer1 ('yes'/'no'), beer2('yes'/'no'), etc.

:pac:

amen · 26-03-2015 9:24pm

hmm I suppose if abv is only a number then you could leave it
for some reason I had text in mind

SQL table question?

Comments