Jump to content

Archived

This topic is now archived and is closed to further replies.

digitalgod

table structure help

Recommended Posts

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?

Share this post


Link to post
Share on other sites
Well, you need a bottles table, a clubs table, and then a relations table with bottleID, clubID, price, and I guess "night".

Share this post


Link to post
Share on other sites
[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

Share this post


Link to post
Share on other sites
[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 ;)

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
That's a FK constraint for InnoDB tables.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
MyISAM tables don't respect FK constraints... otherwise, your code seems correct.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.