CODEWITHSUNDEEP
phpmysqldatabasemodel
Deminetix
Deminetix

Reputation: 2974

Discussion MySQL Database Structure

The project I have going uses multiple "walls" for discussion that are created by the user.

My question pertains to how this should be modelled in the MySQL database.

Would having one table called "walls" that contain the information such as who created it etc, and another table called "messages" that contain each message with a "wall_id" column to distinguish where it belongs, work?

Also, should the "messages" table use the wall_id as the primary key for indexing? Or is having duplicate numbers not useful for primary keying?

I am relatively new to modelling databases "the right way", any suggestions or solid help is greatly appreciated for improving efficiency and ease of working with the data.

Upvotes: 0

Views: 177

Answers (5)

MD. Sahib Bin Mahboob
MD. Sahib Bin Mahboob

Reputation: 20534

Lets break down the scenario one by one :

  1. At first , you should have user table with a user_id as the primary key.
  2. Then you should have a wall table. It's primary key will be wall_id and foreign key will be the user_id from user table as every wall belongs to only one user . user_id will also be a unique key in this table.
  3. A message table will be necessary then. it will store the messages along with a primary key field of message_id.
  4. At last you will need a message_wall table to store the info which message belongs to which table . Here message_id , wall_id will be foreign key and composite primary key.

Hope that helps.

Upvotes: 0

Matthew Daly
Matthew Daly

Reputation: 9476

I would use two tables as you described, one for walls and one for messages, with the primary key for the walls being an autoincremented integer. For the messages, again use an autoincremented integer as the primary key, but it should also have the wall ID as a foreign key.

I recently built a WordPress noticeboard plugin that worked something like that, with each noticeboard being embedded in a page. The pages were already set up by WordPress, so it was just a case of adding a noticeboard table, and each one had its own primary key, but also referred to the WordPress page ID as a foreign key.

You might want to consider getting a decent book on database design or reading a few articles - it'll help you no end. I used 'The Manga Guide to Databases', and it was a bit corny, but it really did help me get my head around how to build a relational databases. I'm sure many other people will be able to recommend good books or articles on this subject too.

Upvotes: 0

Matt Humphrey
Matt Humphrey

Reputation: 1554

Your approach is indeed the correct way to go, except what you have said about the primary key. Primary keys have to be unique. You can have multiple fields as a primary key, but the key itself has to be unique.

So you would have the following tables.

CREATE TABLE `wall` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(10) UNSIGNED NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `user` (`user_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

CREATE TABLE `user` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

CREATE TABLE `message` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `wall_id` INT(10) UNSIGNED NOT NULL,
  `user_id` INT(10) UNSIGNED NOT NULL,
  `message` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `wall` (`wall_id`),
  INDEX `user` (`user_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

You would then relate the user table on user_id and wall on wall_id.

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 4601

Basically Normalize the tables

Users
User_id  Name  otherFields

Category
Category_id  Cat_name

Walls
Wall_id  User_id Cat_name

Messages
Message_id User_id Wall_id Message

Would the Basic thing

Scenario 1 So for display all the wall of all particular user based on user_id and join with the message

Scenario 2 To display all walls to particular category based on category_id

You can extend this based on other thing you would need.

Upvotes: 0

GautamD31
GautamD31

Reputation: 28753

Just create a table named "Wall" and implode wall_id as your primary key

create table wall(wall_id int ,primary key(wall_id));

then create "message" table with foriegn key to wall_id as

create table message(mes_belong_id int,primary key(mes_belong_id),wall_id int,foreign   key(wall_id) references wall(wall_id));

then save each message under same wall id that you need and it automatically stored into message.If you want to retrive messages just use wall_id.That's it.

Upvotes: 0

Related Questions