CODEWITHSUNDEEP
mysqldatabaseprimary-keyentity-attribute-value
something
something

Reputation: 537

Choosing the primary key

I'm doing a project where i have to make a site almost like wikipedia, but while making the database i've stumbled across a problem.

I can't seem to figure out what my primary key should be. I tried my best to illustrate my table structure below, so you can see the problem.

name            | type        | content   |
--------------------------------------
John Doe        | overview    | some text |
----------------------------------------
John Doe        | background  | some text |
----------------------------------------
John Doe        | height      | some text |
----------------------------------------
Fred Flintstone | overview    | some text |
---------------------------------------
Fred Flintstone | background  | some text |

I dont think it would make sense to just make an id column, and auto increment it, as each record is useless alone.

What do you think? is a primary key needed? If so what should it be?

Upvotes: 0

Views: 142

Answers (3)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

Before you do any more work, take a look at mediawiki.org.

In your sample data, {name, type} uniquely identifies each row. But your sample data isn't representative. Names aren't unique in the real world, so you probably need some other way to identify people. Just adding an autoincrementing ID number as the primary key won't work, because it allows nonsense like this.

id  | name            | type        | content   |
--------------------------------------
1   | John Doe        | overview    | some text |
2   | John Doe        | overview    | some text |
3   | John Doe        | overview    | some text |
4   | John Doe        | overview    | some text |

How many distinct people are there? (There are 3. The John Does with id numbers 1 and 3 are the same person.) A UNIQUE constraint on {name, type} won't help, either, because that would allow only one person named "John Doe" in the database.

So what else do you know that you can use to identify people? Email address? (Don't look for a magic bullet here. There isn't one. Even email addresses can be shared. You're just looking for something better than "name".)

Upvotes: 1

Derk Arts
Derk Arts

Reputation: 3460

I would certainly go for an Auto Increment primary ID. That's what its for, it makes it easier to maintain and update the table, and also a lot easier to link to other tables in a relational setup.

There is also the matter of performance when looking up records.

To sum up:

  • Maintaining relations between tables
  • Performance when looking up records.
  • Easier to write update and delete queries when changing the content

See: http://code.openark.org/blog/mysql/reasons-to-use-auto_increment-columns-on-innodb

Upvotes: 4

codemaster
codemaster

Reputation: 572

I would suggest you create an id column as primary key and create one more table that will map the records like overview, background, height

id overview  background height 
1      1           3         5

though this type of schema is not fully normalize. other wise you can make different tables for overview, background and height and then create a relation table like above for them.

Upvotes: 1

Related Questions