Most efficient way to set up MySQL Tables?

2

votes

I have a table called 'users' that contains fields such as

username
DOB
gender
etc

Each user can save their plants on a table called 'plants' that has the following fields

username
plant

So user 'johndoe' would show on table 'plants' as follows:

johndoe -> lilly
johndoe -> orchid1
johndoe -> orchid2
johndoe -> fern1
johndoe -> fern2

Questions:

Is this a bad way of setting up my tables? I ask because when the number of users grows, then there will be hundreds of rows, several of which with repeated usernames because each user will likely have more than one plant. Would this slow down queries?

If I make one plant table per user, I think that would increase dramatically the number of DB requests and create an enormous amount of tables in the DB.

Another option would be to have separate tables for lilly, orchid, fern, etc with fields such as

username

but then I would still have several rows with repeated usernames.

Any suggestions?

mysqldatabase

2

votes

Normalize your tables. Add id field to users and store that id in plants instead of the username.

Further, read more about normalization principles and try to apply them in practice.

1

votes

In stead of using the username I would use an INT id. Like so:

userid
username
DOB
gender
etc

and for your plants:

plantid
userid_fk
plantname

And then join on users.userid = plants.userid_fk to find the matches :)

0

votes

That's a perfectly reasonable way (your initial proposal) to set things up; that's what relational databases are for.

You're doing a classic many-to-many relationship in your plants table. Each user can have several plants, and each plant may have several users.

For better efficiency, you could use an id to refer to your users instead of a user name. In most relational databases, an id is a row number, and is a very efficient (and compact) reference.

0

votes

Here's how I'd go about this, with an example database script and query:

Create/Load Users Table - Using a surrogate primary key instead of the username since queries will be faster against INTs and they can conceivably change.

CREATE TABLE users  ( 
    id          int(11) AUTO_INCREMENT NOT NULL,
    username    varchar(255) NOT NULL,
    gender      char(1) NULL,
    PRIMARY KEY(id)
) ENGINE = MyISAM AUTO_INCREMENT = 3

Load Users

INSERT INTO users(id, username, gender)
  VALUES(1, 'john', NULL)
GO
INSERT INTO users(id, username, gender)
  VALUES(2, 'jane', NULL)
GO

Create Plants

CREATE TABLE plants  ( 
    id      int(11) AUTO_INCREMENT NOT NULL,
    name    varchar(200) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = MyISAM AUTO_INCREMENT = 6

Load Plants

INSERT INTO plants(id, name)
  VALUES(1, 'fern1')
GO
INSERT INTO plants(id, name)
  VALUES(2, 'fern2')
GO
INSERT INTO plants(id, name)
  VALUES(3, 'orchid1')
GO
INSERT INTO plants(id, name)
  VALUES(4, 'orchid2')
GO
INSERT INTO plants(id, name)
  VALUES(5, 'lilly1')

Create Join Table I use a surrogate primary key here since so many web platforms won't properly update a join table with a 2 column primary. Also, I've ended up adding meta data / details to these types of joins quite often, and it gets messy without it. Note I don't restrict the combination of the same user adding the same plant type, but you can easily.

CREATE TABLE plants_users  ( 
    id          int(11) AUTO_INCREMENT NOT NULL,
    plant_id    int(11) NOT NULL,
    user_id     int(11) NOT NULL,
    PRIMARY KEY(id)
)
ENGINE = MyISAM
AUTO_INCREMENT = 8
GO
CREATE INDEX fk_plant_id USING BTREE 
    ON plants_users(plant_id)
GO
CREATE INDEX fk_user_id USING BTREE 
    ON plants_users(user_id)

Load Join Table

INSERT INTO plants_users(id, plant_id, user_id)
  VALUES(1, 1, 1)
GO
INSERT INTO plants_users(id, plant_id, user_id)
  VALUES(2, 2, 1)
GO
INSERT INTO plants_users(id, plant_id, user_id)
  VALUES(3, 3, 1)
GO
INSERT INTO plants_users(id, plant_id, user_id)
  VALUES(4, 4, 1)
GO
INSERT INTO plants_users(id, plant_id, user_id)
  VALUES(5, 2, 2)
GO
INSERT INTO plants_users(id, plant_id, user_id)
  VALUES(6, 3, 2)
GO
INSERT INTO plants_users(id, plant_id, user_id)
  VALUES(7, 5, 2)

Final Query:

select
    users.username,
    plants.name
from
    users,plants,plants_users
where
    users.id = plants_users.user_id and
    plants.id = plants_users.plant_id

Result:

    user    name
    john    fern1
    john    fern2
    john    orchid1
    john    orchid2
    jane    fern2
    jane    orchid1
    jane    lilly1

Most efficient way to set up MySQL Tables?

4 Answers