9

Currently have a table .. and need to start adding new data columns to it. Not every record (even going forward with new data after adding the new data columns) will have data. So I am wondering if this is more suited for a new table since it is really an extension of some of the data rows and not applicable to every row.

In other words, since there will be a lot of unused columns for those new data elements, it seems like this would be more suited for a new table ?

EDIT (figured this was too limited)

The first table is a record of page views (currently 2million records) - id - IP address - times viewed - created_at timestamp - date

for every IP address, a record is made per day - and consecutive pageviews are added to the times views per day

additional field(s) would be for point of origin tracking (ie google analytics source/medium/campaign)

Not every visit will have that info. Im would assume about 10% of the rows will have the data (as it is usually only attributed on the first visit)

The main use for the data would be to attribute where people came from. This may wind up being used more frequently (which then seems to lend itself to the single table)

Appreciate the feedback - can add more if needed

Improve this question
2
  • 1
    You should provide some more information about the nature of the data and how you will use it. Will you be retrieving this data or filtering on it? How many fields are we talking about? How big are the fields? How many rows do you currently have and how many rows will need the new fields? Commented May 25, 2012 at 12:38
  • You should also consider asking this on Database Administrators - database design questions are our bread and butter over there! Commented May 25, 2012 at 12:41

2 Answers 2

Reset to default
12

The basic rule is this (simplified from more stringent normalisation rules).

If the attribute/column depends on the entire primary key and nothing else, it belongs in the table.

If it depends on something other than, or in addition to, the primary key, it belongs elsewhere and the tables it belongs in should have a relationship with the current table.

For example, your name depends on your SSN so, if SSN was the primary key, your name would belong in that table. Your car or phone number does not depend entirely on your SSN (since you may have more than one car or phone, so it would go in a different table (your primary phone number may go in the first table).

If you really want to learn about database design, forget about the syntax of the select command and have a look into normalisation. My advice to others is that all database schemas should start at 3NF and only revert if needed for performance.

And then, only if you understand (and mitigate) the problems inherent in doing that.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for all the input - after reading this thread and the other over at dba.stackexchange.com/questions/18399/… I came up with a different schema that created a a visitor table and a visits table. The visits table keeps track of the SOURCE information and the type of visit. The original page view then links to the visitor and (when applicable) the visit, since not every page view will also record a visit
I'd like to elaborate another example. Consider a shopping site. Will you append an Address column to the User table or will you create a new table? A user may have multiple addresses, so it's a better design choice to create a separate table for addresses. Even if you currently plan to allow a single address per user, it's nice to have a higher DOF.
1

If most of the column are of data type varchar then the approach is fine.

because varchar datatype take the space in table according to size of content in the table cell.

You can define the new column as SPARSE if using Sql server 2008.

Refer to know more about pros and cons of SPARSE Column

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.