Best way to design this layout

Hi All,

I currently have 4 database tables layout shown below:

CREATE TABLE `category` (
  `cat_id` int(11) NOT NULL,
  `cat_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `skills` (
  `skill_id` int(11) NOT NULL,
  `skill_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `skillsUsers` (
  `user_id` int(10) NOT NULL,
  `cat_id` int(10) NOT NULL,
  `skill_id` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
  `user_id` int(10) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My question is, is this the best way to link the associated skills and category’s with users? because a user can be assigned to more than one category and have more than one skill. I stored this information in the “skillsUsers” table.

any advice I would be grateful.

It looks to me like you should be able to use different JOIN queries to get what you what.

One thing I question is the CREATEs

I always explicitly specify a PRIMARY KEY.

AFAIK MySQL can do “magic”
http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-indexes-keys

If you do not have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.

But I don’t usually feel comfortable trusting that the “magic” will do as I expect or want it to.

In this case, I think skillsUsers will need a multiple-column index and therefore need a separate clause

A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(index_col_name, …) clause.

if a user’s skill must ~always~ be associated with a category, then yes

if a user can belong to a category without an associated skill, or to a skill without an associated category, then no

i suspect that skills and categories should actually be uncoupled

1 Like

good for you

but it’s completely unnecessary

1 Like

Thanks for the advice r937. Yes a skill always need a category assigned to it, for the purpose of this application. :slight_smile:

Joe

can the same skill belong to more than one category? and if so, can a user have more than one of those same skills?

i’m still not comfortable with your statement that a user can be assigned to more than one category and have more than one skill

perhaps if you gave some comprehensive examples…

but in the meantime, i guess it will be fine… UNTIL you find some data that won`t work

Is there any reason why this might be a problem?

CREATE TABLE `skillsUsers` (
  `user_id` int(10) NOT NULL,
  `skill_id` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `categoriesUsers` (
  `user_id` int(10) NOT NULL,
  `cat_id` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And then have a categoriesSkills table?

Or perhaps the skillsUsers table would be better named categoriesSkillsUsers to more accurately reflect what it contains?

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.