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.
| Author |
Topic |
|
nobody9999
Starting Member
1 Post |
 Posted - 2006-11-23 : 03:21:14
|
| I have a question about designing the database tables.I am just starting to code in SQL, so please bare with me...I need a table to store the following informationstudentID, studentName and studentPhoneNumber.let say that my application will need to do query to get the studentName only occasionally, let say 20% of the time and the studentName rarely needs to updated.Butthen my application needs to do query to get the studentPhoneNumber very frequently and concurrently by different users, let's say 90% of the time, and the studentPhoneNumber needs to be updated frquently.Should I put everything into one single table, or should I put studentPhoneNumber in a separete table so that the users will only hammer the table which stores the studentPhoneNumber, and leaving the table that stores the studentName open to other users?Does it work this way? Or it doesn't really matter?Thank you in advance |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-11-23 : 03:26:01
|
| Querying a table by a particular user will not lock your table to others (unless they define some specific locking hints). I don't see any need to create a separate table just for one column. Moreover, if user needs some other student information along with phone numbers, there will be join overhead.Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-23 : 03:44:05
|
| If you want a history of all student's phonenumbers, you will have to make a new table.Peter LarssonHelsingborg, Sweden |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-11-23 : 04:26:53
|
| Peter,What do you mean by "history" of phone numbers? Do you mean to say you want to keep track of how many times numbers changed?Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
pootle_flump
1064 Posts |
Posted - 2006-11-23 : 04:32:22
|
| The regular retrieval of the phone number would suggest that you would benefit from a covering index. The regular updating of the phone number would suggest that maintaining that index would be costly. It depends on what sort of ratio of reads and write you will be doing however a covering index might be helpful.I would not split the phone number off to a different table either (unless there is a requirement to retain a history of course). |
|
|
|
|
|
|
|
|
Question about table design and performance.