Search code examples
mysqldatabasenormalizationdatabase-normalization

Should i to normalize this MySQL table

i'm designing a web site for a friend and i'm not sure what's the best way is to go in regards to one of my database tables. To give you an idea, this is roughly what i have

Table: member_profile
`UserID`
`PlanID`
`Company`
`FirstName`
`LastName`
`DOB`
`Phone`
`AddressID`
`website`
`AllowNonUserComments`
`AllowNonUserBlogComments`
`RequireCaptchaForNonUserComments`
`DisplayMyLocation`

the last four AllowNonUserComments AllowNonUserBlogComments RequireCaptchaForNonUserComments DisplayMyLocation

(and possibly more such boolean fields to be added in the future) will control certain website functionality based on user preference.

Basically i'm not sure if i should move those fields to a

new table : member_profile_settings 

`UserID`
`AllowNonUserComments`
`AllowNonUserBlogComments`
`RequireCaptchaForNonUserComments`
`DisplayMyLocation`

or if i should just leave it be part of the member_profile table since every member is going to have their own settings.

The target is roughly 100000 members on the long run and 10k to 20k in the short run. My main concern is database performance.

And while i'm at it question #2) would it make sense to move contact information of the member such as address street, city, state, zip , phone etc into the member_profile table instead of having address table and having the AddressID like i currently have.

Thank you

Solution

  • I would say "no" and "yes, but" as the answers to 1) and 2). For #1, your queries are going to be a lot easier to manage if you create columns for each preference. The best systems I've worked with were done that way. Moving the preferences into a separate table with "user, preference, value" triples leads to complex queries that join multiple tables just to check a setting.

    For #2: there's no reason to put the address in another table, because the single "AddressID" column means there's just one address per member, anyway, and again, it's just going to complicate the queries. If you turn it around backwards and have an address table that embeds userids then that might make sense; it makes even more sense to do phone numbers that way, since people often have multiple phone numbers.