Table relationship - Newbie

[johndale]

Hi, I would like to please someone more experienced to help me figure out one-to-many relationship in InnoDB tables.

I have following 3 tables>

Poll
--id(PK)
--question varchar()
--created datetime
--created_by ( FK to User.id ) [b]Many-to-One[/b] relation ( Many POLLS have one USER ), am I right here ? Or am I missing concept ?

Answer
--id(PK)
--answer varchar()
--poll_id (FK to Poll.id ) [b]Many-to-One[/b] relation (Many ANSWERs have one POLL )

User
--id(PK)
--username varchar()
etc...

Now when I access Poll table in php script I want correct Answers to be connected as well. So I gues that it is ONE-to-MANY relationship. And I do not know how to define it with use of foreign keys (in InnoDB) ? I mean what FK do I have to define in Poll table to be able to access selected Answers according to selected Poll ?

Please if you know the answer, help me. Thanks much for help & advices.
Have a nice day.
jd.

[obsidian]

You definitely have the concept down. Keep in mind, though, that the constraints you put on the table do not assist much in your queries... you'll still have to run joins to do that, but they do [i]constrain[/i] you to be able to only insert appropriate data when you run insert, update and delete queries. If you're looking to create the SQL for a foreign key constraint, you should be able to simply do the following in your create table statement:
[code]
CREATE User (
  id integer unsigned primary key auto_increment,
  username varchar(20) unique
);

CREATE Poll (
  id integer unsigned primary key auto_increment,
  question varchar(255),
  created datetime,
  created_by integer REFERENCES User(id) ON UPDATE CASCADE ON DELETE CASCADE
);
[/code]

The update and delete clauses are definitely optional, but when there is content that is dependent on the foreign key constraint, not only referencing it, I like to remove it when the anchor for that FK is removed as well.

Hope this helps.

[johndale]

Thank you very much Obsidian for advices&help !!

May I ask just one more question ?
How to define relations between Poll - Answer tables ?

I want to have this>

Poll [b]has many[/b] Answer(s), so it is one to many rel., am I right ? I mean I want to have form when I create/edit poll question, but I also will have access to create/edit right answers (associated with Poll). [b]So what FK and where I have to define [/b]?? Sory if this is stupid question, but I got stuck on this, and can't figure it out...

Again thanks very much for help !
jd.

[obsidian]

It's the same principle as the previous one. Once your have your Polls table defined, you can define a FK in your Votes table, too:
[code]
CREATE TABLE Votes(
  id integer unsigned primary key auto_increment,
  -- any other fields you want here --
  poll integer REFERENCES Polls(id)
);
[/code]