FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Tables





davidv
I want to have a one to many relationship between two tables.

I have two tables A and B. Rather than having multiple instances of B point to a single A, I want A to point to multiple instances of B. How can I do this?
sonam
Did you talk about Mysql? And without tables example is very heavy to write right code because there is lot of ways to do it same think.

Sonam
davidv
Yep, MySQL.

This is only a draft. I haven't tried running it and seeing as how I haven't touched databases for over half a year, it probably won't work but here it is purely as an example:

Code:
drop database if exists codemonkey;
create database codemonkey;
use codemonkey;

create table projects (
  project_id smallint unsigned auto_increment,
  name varchar(30),
  start_date date,
  finish_date date,
  version varchar(20),
  project_type enum("personal", "web", "game", "uni", "other"),
  source varchar(),
  description text,
  open_source boolean,
  completed boolean,
  leader smallint,
  primary key (project_id),
  foreign key (leader) references accounts (account_id)
) engine = innodb;

create table teams (
  team_id smallint unsigned auto_increment,
  project_id smallint,
  mem1 smallint,
  mem2 smallint,
  mem3 smallint,
  mem4 smallint,
  mem5 smallint,
  primary key (team_id),
  foreign key (mem1, mem2, mem3, mem4, mem5) references accounts (account_id),
  foreign key (project_id) references projects (project_id)
) engine =innodb;

create table accounts (
  account_id smallint unsigned auto_increment,
  fname varchar(20),
  lname varchar(20),
  description text,
  position varchar(20),
  primary key (account_id)
) engine = innodb;


The ER diagram is as follows:

Projects (one) Teams
Teams (zero or more) Accounts
Accounts (zero or more) Teams

By default each team can only have 5 members (I'm not quite sure if that's the proper way to do so). I want a more dynamic table or is that impossible?

I'm quite sure there are syntax errors somewhere so if anyone can see any, point it out Smile
Fire Boar
There is one team per project, right? So why are you linking the "team leader" account rather than the team itself? Better would be to just reference the team ID, and if necessary store the team leader in the "teams" table (defaulting to null).

As for linking teams and accounts together, what you have is a many-to-many relationship, which requires an additional table. SQL does not allow one field to reference multiple rows, ever. Here's how it's done:

Teams >-< Accounts

Becomes

Teams >- Team_Members -< Accounts

Team_Members is a new link table consisting of fields describing the relationship between an account and a team. Often this is simply just the primary keys from the other two tables (team_id and account_id), but you might need other fields too (joined_on, is_executive for instance). Now to have an account as part of a team, you just need to list the [account,team] tuple in the Team_Members table.

In link tables, you should never use ON DELETE SET NULL or ON DELETE SET DEFAULT. Usually you should use ON DELETE CASCADE.
Hogwarts
davidv wrote:
I have two tables A and B. Rather than having multiple instances of B point to a single A, I want A to point to multiple instances of B. How can I do this?


Why? That sounds totally illogical
davidv
I seem to be a little bit confused.

I've made team leader part of team, removed it from projects
I've added a new table team members but I'm not sure what you meant by:

Quote:
Now to have an account as part of a team, you just need to list the [account,team] tuple in the Team_Members table.


Is this what you mean? Each team member table has a reference to the team and 4 additional fields each pointing to an account.

Code:
-- NOT A WORKING COPY
drop database if exists codemonkey;
create database codemonkey;
use codemonkey;

create table projects (
  project_id smallint unsigned auto_increment,
  name varchar(30),
  start_date date,
  finish_date date,
  version varchar(20),
  project_type enum("personal", "web", "game", "uni", "other"),
  source varchar(),
  description text,
  open_source boolean,
  completed boolean,
  team_id smallint,
  primary key (project_id),
  foreign key (team_id) references teams (team_id)
) engine = innodb;

create table teams (
  team_id smallint unsigned auto_increment,
  project_id smallint,
  team_leader smallint,
  members_id smallint,
  primary key (team_id),
  foreign key (team_leader) references accounts (account_id),
  foreign key (project_id) references projects (project_id),
  foreign key (members_id) references team_members (members_id)
) engine = innodb;

create table team_members (
  members_id smallint unsigned auto_incremenet,
  team_id smallint,
  mem1 smallint,
  mem2 smallint,
  mem3 smallint,
  mem4 smallint,
  foreign key (team_id) references teams (team_id),
  foreign key (mem1, mem2, mem3, mem4) references accounts (account_id)
) engine = innodb;

create table accounts (
  account_id smallint unsigned auto_increment,
  fname varchar(20),
  lname varchar(20),
  dob date,
  email varchar(20),
  description text,
  position varchar(20),
  primary key (account_id)
) engine = innodb;


And also why should I never use ON DELETE SET NULL or ON DELETE SET DEFAULT and to usually use ON DELETE CASCADE?
Hogwarts
Why are you using
Quote:
Code:
create table team_members (
  members_id smallint unsigned auto_incremenet,
  team_id smallint,
  mem1 smallint,
  mem2 smallint,
  mem3 smallint,
  mem4 smallint,
  foreign key (team_id) references teams (team_id),
  foreign key (mem1, mem2, mem3, mem4) references accounts (account_id)
) engine = innodb;


and not a many-many relationship between team and accounts? Change your team_members table to
Code:
create table team_member (
  team_id smallint,
  member
  foreign key (team_id) references team (team_id),
  foreign key (member) references account (account_id)
) engine = innodb;


and it becomes infinitely cleaner; you'll just need to have four records if you want four members (however you can have thousands of users, or only one user, depending on how many records you add to this table).
davidv
Hogwarts wrote:
Why are you using
Quote:
Code:
create table team_members (
  members_id smallint unsigned auto_incremenet,
  team_id smallint,
  mem1 smallint,
  mem2 smallint,
  mem3 smallint,
  mem4 smallint,
  foreign key (team_id) references teams (team_id),
  foreign key (mem1, mem2, mem3, mem4) references accounts (account_id)
) engine = innodb;


and not a many-many relationship between team and accounts? Change your team_members table to
Code:
create table team_member (
  team_id smallint,
  member
  foreign key (team_id) references team (team_id),
  foreign key (member) references account (account_id)
) engine = innodb;


and it becomes infinitely cleaner; you'll just need to have four records if you want four members (however you can have thousands of users, or only one user, depending on how many records you add to this table).


Thanks. I wasn't sure to how to create a many to many relationship. Now I know, kudos.
Fire Boar
Looks like you got the many-many thing sorted thanks to Hogwarts.

davidv wrote:
And also why should I never use ON DELETE SET NULL or ON DELETE SET DEFAULT and to usually use ON DELETE CASCADE?


The reason for this is that setting to a default or null value when either a user account or a team is removed from the database is illogical. The whole point of the link table is to establish links between records in two tables. If one side of the link is removed, then there is no point in keeping the link. SQL/InnoDB prevents foreign key dependencies from being violated using the ON DELETE and ON UPDATE clauses to decide what to do. By default, if you try to remove a record being referenced by another table (e.g. removing a team which has one or more members), the delete is rejected. ON DELETE CASCADE is usually more desirable for link tables: it removes everything that depends on the row being deleted (e.g. when removing a team, the database forgets all member links to that team by removing all rows in team_members containing that team ID).
Related topics
CSS OR TABLES
I cant install php chat tables nor set up diffrent subdomain
Top 10 web design mistakes!..
How can i flush my tables
PHP tables
HTML tables with irregular rows and/or columns
Frames, Tables, iFrames
W3 Validation and Google & Tables and DIV
Creating a layout complete with css
What's the best for page layout? CSS or using Tables?
E-Lyrics
Divs and Tables
What happened on my tables?
Help me out with tables, please.
How to make tables with curved borders?
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.