Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 15th, 2010, 10:26 AM
Authorized User
 
Join Date: Mar 2009
Posts: 74
Thanks: 5
Thanked 0 Times in 0 Posts
Default I Need some assistance on database design

I have a hotel database which also includes the following entities:

1) Customers
2) Reservations
3) Shops
4) Restraunts
etc.

Now, the hotel has more than 1 shop and also more than 1 restraunt
and customers are able to make room reservations.

Here is my question.Is it the right design for the entities
Reservations,Shops and Restraunts to all use one customers table
or is it better if each of the entities (Reservations,Shops and Restraunts) has its own customers table. If they all used thesame customers table would thr be too much concurrency issues, and I was thinking the table wd grow too large n cause performance issues. I am quite a beginner in database design...thanks for any contribution...
 
Old January 15th, 2010, 11:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Hi there..

I don't know what kind of concurrent problems will you have, but If I go to a hotel and I have to give my data in every part of it, and also the shop where I'm buying doesn't have my room info (or need to go to another table to match it), then I will definitive think that there is a problem there...


You should have only one customer table.
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old January 16th, 2010, 03:27 PM
Authorized User
 
Join Date: Mar 2009
Posts: 74
Thanks: 5
Thanked 0 Times in 0 Posts
Default Thanks

Year I understand what u mean, I was just thinking with all the other tables trying to access same table, their would be high chances of deadlocks.So I guess ur reply is that I should have one customers and then join to it, and use explicit transactions to minimize dead locks? I just wanted to be clear about it.....cuz am actually new in developing multiuser (intranet) applications....I still welcome more,thanks...
 
Old January 16th, 2010, 08:38 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

No, you misunderstand how DBs work.

A deadlock would only happen if you did operations over a period of time in sequence and if you locked tables as you went, rather than obtaining all the needed locks as you started.

But in any case, I can't see how a customer table would be involved in a deadlock. I would assume that for all operations except maybe a purchase, the customer table would be read-only. That is, why would you need to write to the customer table just because a customer made a reservation at the restaurant???

And, actually, I can't see that even a purchase should cause a write to the cutomer table; instead, it should write a record to maybe the customerBilling table. And since each record in the billing table represents one purchase, you would never have the situation of the Restaurant trying to modify a record that the Giftshop (for example) was in the process of writing.

I just can't see how, under normal operations, you'd ever get a deadlock situation. Sure, deadlocks can happen. And if you really can identify a situation where that could occur, then you just have to make sure that you avoid them by doing an all-or-nothing set of locks. That is, if you needed to lock 3 different records, you would try to get the locks on all of them and, if you couldn't, you'd release all that you had gotten, wait a brief while (milliseconds, most likely) and try again. But for a normal shopping style DB, I really doubt that you *can* find a deadlock situation.
 
Old January 18th, 2010, 03:47 PM
Authorized User
 
Join Date: Mar 2009
Posts: 74
Thanks: 5
Thanked 0 Times in 0 Posts
Default Thanks, this is how I imagined the situation.

Thanks, this is how I imagined the situation. The hotel wants to keep track of all its income through this system. This means some customers that visit the for example,
the giftshop, or restraunt may be staying in the hotel and some customers may not be staying but just came around to have a meal or make some purchase For those that are currently staying in the hotel, their record is already in the customers and reservations tables, therefore their information may be retrieved by their reservationID and they are billed. Now for customers that aren't staying in the hotel but only came in to make a purchase, when they make payments, their info is collected and used to create a record in the customers table and the customers billingtable and an invoice is generated for them, this is why I was thinking that there could be deadlock issues since records are inserted into the customers table for those making room reservations and those that arent staying in the hotel but only came to shop or have a meal (these wd have their info taken at the shop they are making the purchase from, which wd then insert into the customers table)
This is how I designed the database.If this isn't how it works,i'd welcome a much better suggestion.Cuz am just trying to get things real clear, as am not doing this on request but am a beginner and am trying to accomplish this cuz its big enuff a challenge to let me learn much in building the application succesfully and making itas real as possible. Thanks.
 
Old January 19th, 2010, 10:15 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

You really are worrying about stuff you shouldn't need to, honest.

Let's take your suggested scenario, with two disparate things happening at *exactly* (to the millisecond! however unlikely that might be) the same time:

guest buys food in restaurant
non-guest buys food in restaurant

guest sequence:
-- insert into restaurant( guestid, salescheckid, amount ) values( 33, 71381, 87.90 )
-- [get the id of that just added record as "restaurantSaleId"]
-- insert into billing( guestid, typeofcharge, chargeid, amount ) values( 33, 'room charge', restaurantSaleId, 87.90 )

non-guest sequence:
-- insert into restaurant( guestid, salescheckid, amount ) values( 0, 71382, 64.20 )
-- [get the id of that just added record as "restaurantSaleId"]
-- insert into billing( guestid, typeofcharge, chargeid, amount ) values( 0, 'mastercard', restaurantSaleId, 64.20 )

Where is the potential for deadlock there????

The two sequences use ENTIRELY different records. None of those operations depend on information from the other sequence.

It is *only* when two sequence of operations in different "threads" need to make dependent changes on the *same resource* that you have a potential for deadlock.

By the way; I used a guestid of zero (0) to indicate a non-guest. There is no point in adding every non-guest to your Customers table. Besides, if a customer paid for his lunch with cash,how would you ever even *get* information about him? You don't know his name, even.

Anyway, I just really feel you are over-analyzing this.

*****************

ALSO:

If you wrote those two sequences each as a single stored procedure (assuming you aren't using Access as you DB), then even if there *was* a potential for deadlock it wouldn't happen.
 
Old January 21st, 2010, 04:12 PM
Registered User
 
Join Date: Jan 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

First post.

Old Pedant and gbianchi are correct. Just to expound on this a little.

Even if you have to report on, or facilitate the reporting on these statistics via a live data Web reporting portal, or reporting services WHILE people are purchasing and you are inserting into your tables, your SELECT statements that would populate the reporting stored procedures can be used with the transact sql SELECT * FROM CUstomers c WITH (NOLOCK) INNER JOIN Restaurant r WITH (NOLOCK) ON c.[joinedfield] = r.[joinedfield] or whatever. Even if stuff happens down to the millisecond, you should not interfere with your tables continuing to update.

In database design, a general rule of thumb is simply this: Don't put the same data in more than one place if you can help it. In this case, especially with proper indexing, you should be fine.

One customers table =)
 
Old January 21st, 2010, 06:44 PM
Authorized User
 
Join Date: Mar 2009
Posts: 74
Thanks: 5
Thanked 0 Times in 0 Posts
Default Thanks

Thanks for the answers, I guess thats the way to prevent the select statements from putting a shard lock on the table...

Thannks again...





Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Design cf2006 BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 4 August 2nd, 2006 03:50 PM
Database Design Help malhyp BOOK: Access 2003 VBA Programmer's Reference 1 November 3rd, 2005 06:33 PM
Re: Database design malhyp BOOK: Expert One-on-One Access Application Development 0 August 24th, 2005 06:34 AM
Database design, please help! mumick SQL Server 2000 0 January 13th, 2005 02:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.