Php/mySQL database for GUI

Question

Just a general database design question - no programming, but a bit of problem solving I can't quite get my head around...

I have a set of pages for a GUI, which each contain a set of 'modules' - basically styled content boxes containing a few functions/information relevant to the page.

Both the pages and modules are instantiated as php objects before being rendered. I want the variables for these objects (title, content etc...) to be called from a database.

What I can't work out is how to store this data in a database. I have a table where each entry is a page, and a table where each entry is a module, but I can't work out how to store the data representing the modules that each page has, particularly considering the modules may be common to more than one page.

Nested tables would be the ideal answer, does mySQL allow this?

maybe showing your schema / outline of what values to store, might help visualize your solution. — Jakub
– Jakub, Commented Jan 13, 2011 at 5:03

Matthew Scharley · Accepted Answer · 2011-01-13 05:10:09Z

2

This sounds like a many-to-many relationship. You need a third 'linking' table. So, for instance, if you have the following tables:

CREATE TABLE pages (
  id INT(11) NOT NULL, 
  title VARCHAR(40), 
  PRIMARY KEY (id)
);
CREATE TABLE modules (
  id INT(11) NOT NULL, 
  title VARCHAR(40), 
  PRIMARY KEY (id)
);

You would create the third table like:

CREATE TABLE pages_modules (
  pid INT(11) NOT NULL, 
  mid INT(11) NOT NULL, 
  PRIMARY KEY(pid, mid)
)

From here you can add modules to different pages by creating an entry in this table for each page/module pair.

edited Jan 13, 2011 at 5:10

answered Jan 13, 2011 at 5:04

Matthew Scharley

133k55 gold badges199 silver badges225 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dansalias Over a year ago

Cheers mate, definitely seems to be the only way to do it. Thanks so much for that!

Collectives™ on Stack Overflow

Php/mySQL database for GUI

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related