Schema design question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Schema design question

Author

Topic

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-01-18 : 15:55:44

I'm working on a system where they want to add communication info for clients (phone, email, url) to the db. Data volume is relatively small 1k to 50k clients. What are your thoughts on having a single table with the three data columns (given 1 or 2 of the columns may be null) or three tables dedicated to the type of data and a table will only contain a row for a client if there is data of that type?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-18 : 16:08:14

One table is fine for the information you're storing. 3 tables is not necessary here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-01-18 : 16:17:45

Thanks Tara, my thoughts also. I have a colleague that thinks three tables is the proper design. I was just looking for opinions from those more experienced.

Thanks again

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-18 : 18:43:34

One table is fine as long as you force them to have 0 or 1 phone, email and url. If they have to have at least one, then

CHECK ((phone + email + url) IS NOT NULL)

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources