CODEWITHSUNDEEP
mysqldatabaseschema
TIMEX
TIMEX

Reputation: 272274

How would I design this MySQL schema?

I'm building a social network. For each user, I'm going to expect a lot of information about them. Relationship status, work, hometown, school, etc. I'm sure these profile attributes will grow and grow in the future.

Should I store them all on 1 table, with each attribute as a column?

Or should I create 1 table for "user profile", with the basic information...and then have other tables (school table, relationship table, work table, etc) Foreign Key to that?

My question is: What is the best practice when separating into multiple tables?

Upvotes: 2

Views: 1002

Answers (3)

Prescott
Prescott

Reputation: 7412

User
    UserId
    Name
    OtherRelativelyStraighforwardUserInformation

Attribute
    AttributeId
    AttributeText

UserAttributes
    UserId
    AttributeId
    Value

This allows you to expand your attributes relatively easily without having to modify your schema. There are issues if your attributes might be text vs integer vs something else, so it's not perfect, but it's pretty simple.

Upvotes: 0

Greg Hewgill
Greg Hewgill

Reputation: 994619

One way to do this is to store the user attributes in a table separate from the main "user" table. This attribute table would have three columns, "user id", "attribute type", and "value". The attribute type can be some identifier that specifies whatever attribute you like (hometown, school, etc).

User table
------------
UserId
Name

UserAttribute table
---------------------
UserId
AttributeId
Value

Attribute table
------------------
AttributeId
AttributeName

Once you have this structure set up, adding a new attribute is as simple as adding a new row to the Attribute table describing the attribute. Then fill in the attribute for users in the UserAttribute table as needed. No need for expensive ALTER TABLE operations to add new columns.

Upvotes: 3

Johnie Karr
Johnie Karr

Reputation: 2822

Best practice (in my opinion) is to have relational database design.

Users Table
-----------
 UserId
 RelationshipId
 StatusId
 WorkId
 HometownId
 SchoolId

Relationships Table
-----------
 RelationshipId
 RelationshipText

etc...

Upvotes: 0

Related Questions