Jump to content


Photo

table structure help


  • Please log in to reply
8 replies to this topic

#1 digitalgod

digitalgod
  • Members
  • PipPipPip
  • Advanced Member
  • 374 posts

Posted 21 September 2006 - 12:09 AM

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?

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 September 2006 - 12:42 AM

Well, you need a bottles table, a clubs table, and then a relations table with bottleID, clubID, price, and I guess "night".
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 21 September 2006 - 12:47 AM

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

agreed. here is the idea spelled out a little more:
--
-- 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)
);

hope this helps
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 September 2006 - 12:49 AM

obsidian to the rescue...
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 21 September 2006 - 12:50 AM

obsidian to the rescue...


lol... i think we've been racing with answers for some of these ;)
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#6 digitalgod

digitalgod
  • Members
  • PipPipPip
  • Advanced Member
  • 374 posts

Posted 25 September 2006 - 06:04 PM

thanks guys I'll give it a shot,

what does

bottle_id int4 REFERENCES bottles(id),

do and how will I query/update etc the tables?

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 September 2006 - 06:27 PM

That's a FK constraint for InnoDB tables.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 digitalgod

digitalgod
  • Members
  • PipPipPip
  • Advanced Member
  • 374 posts

Posted 25 September 2006 - 07:01 PM

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

$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()); 


#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 September 2006 - 08:46 PM

MyISAM tables don't respect FK constraints... otherwise, your code seems correct.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users