table structure help

[digitalgod]

hey guys,

I'm trying to figure out what would be the best way for creating a table structure..

I need to have a list of bottles that I can add myself via an admin panel, the cost of the bottle depends on the night and the club

So let's say I have a list of 100 bottles, 1 club might only have 20 of those bottles and it has it's own price for each of those bottles depending on the night

don't know if I'm making any sense... does anyone know what would be a good structure where all tables are linked together?

[fenway]

Well, you need a bottles table, a clubs table, and then a relations table with bottleID, clubID, price, and I guess "night".

[obsidian]

[quote author=fenway link=topic=108864.msg438415#msg438415 date=1158799343]
Well, you need a bottles table, a clubs table, and then a relations table with bottleID, clubID, price, and I guess "night".
[/quote]
agreed. here is the idea spelled out a little more:
[code]
--
-- Bottle table
--

CREATE TABLE bottles (
  id int4 primary key auto_increment
  name varchar(20)
);

--
-- Clubs table
--

CREATE TABLE club (
id int4 primary key auto_increment,
name varchar(40)
-- any other club information here
);

--
-- Prices Table
--

CREATE TABLE prices (
bottle_id int4 REFERENCES bottles(id),
club_id int4 references clubs(id),
night int2, -- I would recommend using the numeric day of the week for ease of recording
price float(6,2)
);
[/code]

hope this helps

[fenway]

obsidian to the rescue...

[obsidian]

[quote author=fenway link=topic=108864.msg438426#msg438426 date=1158799789]
obsidian to the rescue...
[/quote]

lol... i think we've been racing with answers for some of these ;)

[digitalgod]

thanks guys I'll give it a shot,

what does [quote]bottle_id int4 REFERENCES bottles(id),[/quote] do and how will I query/update etc the tables?

[fenway]

That's a FK constraint for InnoDB tables.

[digitalgod]

I'm really going to sound very newbish lol but that doesn't really mean anything to me :S
All of my tables are MyISAM so will it affect anything?

And say I add a price for a bottle would it look something like this

[code=php:0]
$name = $_POST['bottle']; //15 (id of bottle)
$club = $_POST['club']; // 7  (id of club)
$night = $_POST['night']; // 05
$price = $_POST['price'];

mysql_query("INSERT INTO prices(bottle_id,club_id,night,price) VALUES('$name','$club','$night','$price')") or die(mysql_error());
[/code]

[fenway]

MyISAM tables don't respect FK constraints... otherwise, your code seems correct.