Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0
This is the forum to discuss the Wrox book ASP.NET 2.0 Website Programming: Problem - Design - Solution by Marco Bellinaso; ISBN: 9780764584640
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 2nd, 2008, 03:35 AM
Authorized User
 
Join Date: Mar 2008
Location: New York, NY, USA.
Posts: 62
Thanks: 1
Thanked 3 Times in 3 Posts
Send a message via Yahoo to kalel_4444
Default SQL Design: Foreign Key to Multiple Primary Keys?

I'll keep this as short as possible in hopes that it's understandable.

I'm creating a Review Module and basing the structure on the Articles Module from TheBeeHouse. Nothing new to the NET, e.g. "Amazon Reviews"

In the BeerHouse there is a FK from tbh_Categories --> tbh_Articles.
In my application I have multiple Category tables (Hotels, Restaurants, Shows) with their own respective HotelID, RestaurantID, ShowID and each with different table info so combining the tables into 1 is impossible.

Whereas TheBeerHouse only maps tbh_Categories.CategoryID = tbh_Articles.CategoryID, my case would need to map to 1 of 3 possible tables.

Here is the relationship between my new designed tables and TheBeerHouse:
my_Reviews is like the table tbh_Articles
my_Hotels is like the table tbh_Categories
my_Restaurants is also like the table tbh_Categories
my_Shows is also like the table tbh_Categories

So the goal is not to query 3 tables to map 1 "ProfileID" (whether it's a HotelID, RestaurantID, or ShowID)

I thought of a mapping/conduit table to link it, I just haven't figured out how to piece it together.

I'm open to any suggestions.

Thank you,
Ronnie


  #2 (permalink)  
Old May 2nd, 2008, 09:48 AM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

If you could post the details of the schemas of these tables (i.e. the columns for each table) it might make it easier to understand what you are trying to achieve.

  #3 (permalink)  
Old May 2nd, 2008, 10:21 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Creetown, UK
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
Default

Quote:
quote:Originally posted by kalel_4444
 I'll keep this as short as possible in hopes that it's understandable.

I'm creating a Review Module and basing the structure on the Articles Module from TheBeeHouse. Nothing new to the NET, e.g. "Amazon Reviews"

In the BeerHouse there is a FK from tbh_Categories --> tbh_Articles.
In my application I have multiple Category tables (Hotels, Restaurants, Shows) with their own respective HotelID, RestaurantID, ShowID and each with different table info so combining the tables into 1 is impossible.

Whereas TheBeerHouse only maps tbh_Categories.CategoryID = tbh_Articles.CategoryID, my case would need to map to 1 of 3 possible tables.

Here is the relationship between my new designed tables and TheBeerHouse:
my_Reviews is like the table tbh_Articles
my_Hotels is like the table tbh_Categories
my_Restaurants is also like the table tbh_Categories
my_Shows is also like the table tbh_Categories

So the goal is not to query 3 tables to map 1 "ProfileID" (whether it's a HotelID, RestaurantID, or ShowID)

I thought of a mapping/conduit table to link it, I just haven't figured out how to piece it together.

I'm open to any suggestions.

Thank you,
Ronnie


Ronnie,

further to what lee has noted re schema info, you might want to try downloading the templates that i created a while back for the site generator which deals with this very issue (i.e. multiple child classes based on FK/PK combinations). THe requirements for this template to work are:

1. PK created (as expected) on primary table
2. FKs created on each related table
3. secondary indexes creted on FKs (indexes don't get created by default when creating FK's)

If you're interested in trying it out (sqlserver 2005 only), then you can grab the 'bag' from here:

http://www.originaltalent.com/downlo...atesAndDLL.zip

and just copy the new templates folder and dll into the flixon folder.

let me know if you have any issues.



jimi

http://www.originaltalent.com
  #4 (permalink)  
Old May 2nd, 2008, 06:50 PM
Authorized User
 
Join Date: Mar 2008
Location: New York, NY, USA.
Posts: 62
Thanks: 1
Thanked 3 Times in 3 Posts
Send a message via Yahoo to kalel_4444
Default

Below is a partial work up of my table schema.

Again, the goal is a Review Module. Users writing reviews for particular Hotels, Restaurants, or Broadway Shows. Which is similar to tbh_Articles, but instead of attaching it to a CategoryID in tbh_Categories, it will be attached to one of/either a HotelID in my_Hotels, RestaurantID in my_Restaurants, or ShowID in my_Shows.

Goto the bottom of this Amazon page to "Customer Reviews" and you'll see the idea.
http://www.amazon.com/ASP-NET-2-0-We...9761489&sr=1-4

As you can see from the Amazon page, multiple Customer Reviews are listed on a single page, using a query I would assume much like the [tbh_Articles_GetArticlesByCategory] SPROC.


my_Hotels my_Restaurants my_Shows my_Reviews

HotelID(PK) RestaurantID(PK) ShowID(PK) ReviewID (PK)
HotelName RestaurantName ShowName AddedDate
Address1 Address Address AddedBy
City City City Title
CheckIn CuisineType ShowType Body
CheckOut Price OnBroadway ProfileID (FK)(To HotelID/RestaurantID/ShowID)

If I only had 1 table, lets say Hotels, It would not be a problem, I'd so something like:

Code:
SELECT my_Reviews.ReviewID, my_Reviews.HotelID, my_Hotels.HotelName AS HotelName
FROM my_Reviews INNER JOIN my_Hotels
    ON my_Reviews.HotelID = my_Hotels.HotelID
WHERE my_Reviews.HotelID = @HotelID
But in my case, I also have a Restaurants and Show tables (and thats just the beginning).
I don't know if Amazon has 1 table for all there products (books, DVD's, Electronics), I doubt it..!

So, do I create a seperate Review Module, for each table? Doesn't seem like a smart design.

I'm in the beginning design phase so I have no restrictions, I can add or change any table or field without comprimising any SPROC's or Code since I haven't created any yet. If there is a completely different design structure in my mind, I'm open to hear them.

Once again, thank you,
Ronnie

  #5 (permalink)  
Old May 3rd, 2008, 04:11 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Creetown, UK
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
Default

Ronnie,

Off of the top of my head (and i'll elaborate once i've considered it more fully), it looks to me like you'll need another table that forms a link with my_reviews and the other tables. this might in theory be defined as the following:

my_Review_type
---------------------
ReviewTypeID [PK]
TypeDescription (i.e. Hotel, Book, Film, Restaurant etc)

...(there would be other supporting data fields of course)

Then, add a new additional column ReviewTypeID [FK] to each of the tables my_Hotels, my_Restaurants, my_Shows, my_Review.

in the case of my_Review, the new structure would look like this:

my_Review
----------------
ReviewID [PK]
ReviewTypeID [FK]
ProfileID [FK (not sure if this can be an FK in this case)] (id from related table i.e. my_Hotels, my_Restaurants, my_Shows etc)
AddedDate
AddedBy
Title
Body

when querying the reviews attached to a particular ProfileID, you'd add into the where clause, logic that was related to the table that was attaching to the reviews (you may even have an internal enum in the class that held this info, such as :??)

    public enum ReviewType
    {
        restaurant, hotel, film, play
    }

of course, another initial consideration is the relationships between your primary tables i.e. a restuarant COULD be a part of a hotel, a show could be in the hotel etc, etc... Also, the address details should be de-normalised from the base tables as (in the example of my_Hotel) the hotel could have several addresses (one for booking vs the actual venue address etc, etc)

this is just my initial (rambling) thoughts remembering that i haven't yet had breakfast :)

[edit] looking at the blogo.net project, there is a similar idea going on where entries are added to a table called blogentries where the type is either articles or blogentry. you might want to take a look at how that's implemented for ideas. the article that describes the process as well as a download is here:


http://dotnetslackers.com/articles/a...fBlogoNET.aspx


jimi

http://www.originaltalent.com
  #6 (permalink)  
Old May 6th, 2008, 12:04 AM
Authorized User
 
Join Date: Mar 2008
Location: New York, NY, USA.
Posts: 62
Thanks: 1
Thanked 3 Times in 3 Posts
Send a message via Yahoo to kalel_4444
Default

Hi Jimi,

Thanks for the reply. I've been working through your post on and off the entire weekend, I think I understand what you're saying, but I'm still questioning how to write the query? Even if I add the my_Review_type table and enum class, how do I know which table to INNER JOIN?

In trying to figure you're theory out, I came up with a dynamic solution. But I don't think that was your idea, and I really don't want to use dynamic queries If I don't have too.

Thanks again,
Ronnie

  #7 (permalink)  
Old May 7th, 2008, 07:57 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Creetown, UK
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
Default

Ronnie,

I've created a little test app that you can run over that might get you a step closer to what you describe above. I haven't really changed your structure too much, tho' it became obvious while creating this that a change in structure really will be required to give you the flexibility and 'growth' that will be needed as you move fwd.

anyway, you can download it (and copy the extracted database to your Microsoft SQL Server\MSSQL.1\MSSQL\Data\ folder and then attach it under the name TravelReview). Thereafter, you should be able to browse the structure of the database as well as the code in vs2008.

Here's the link to the archive (update to version 2):

http://www.originaltalent.com/downlo...velReview2.zip

let me know if you have any issues.

jimi

http://www.originaltalent.com
  #8 (permalink)  
Old May 7th, 2008, 08:43 PM
Authorized User
 
Join Date: Mar 2008
Location: New York, NY, USA.
Posts: 62
Thanks: 1
Thanked 3 Times in 3 Posts
Send a message via Yahoo to kalel_4444
Default

Jimi,

Thanks for the test app, I didn't mean for you to create a whole app, but either way thanks for going over and above.. :)

In conclusion, I see the key method is:

Code:
GetReviewsReviewTypeID(ReviewTypes reviewType, int profileID, string sortExpression, int startRowIndex, int maximumRows)
Finally, what were your thoughts when you said...
Quote:
quote:I haven't really changed your structure too much, tho' it became obvious while creating this that a change in structure really will be required to give you the flexibility and 'growth' that will be needed as you move fwd.
I'd like to anticipate as many issues and ideas early on, so I'd be interested in your thoughts.

Once again, thank you Jimi,
Ronnie

  #9 (permalink)  
Old May 8th, 2008, 03:49 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Creetown, UK
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
Default

Ronnie,

Glad you got some benefit from the little app. To be honest, it wasn't too much effort as most of it was 'templated' via the
flixon site generator anyway and then hand tweaked for the last 10% .

Ok, onto the issue regarding the table structure. I have a few questions on this and will start with the my_Hotels table (the
others have similar questions attached to them). On this table, you have a definition of:

HotelID
HotelName
Address1
City
Checkin
CheckOut

I am assuming this to be the main hotel row for the establishment. In this case, can i clarify that CheckIn and CheckOut
fields are related to the hotel's 'rules' regarding these times (i.e. 14:00 checkin and 12:00 checkout etc...)?? On the
assumption that this is the case, then for hotels, i'd envisage a structure that is slightly more de-normalised as detailed below:

my_Hotel
----------
HotelID
EntityType (this had been called ReviewType)
HotelName
Checkin
CheckOut
StartDate (i.e. when the hotel opened initially for business)
EndDate (this would default to something like 12/31/2037)

my_Address (probably wouldn't happen, but an address can be shared by many departments)
----------
AddressID
AddressLine1
AddressLine2
AddressLine3
City
ZipCode
GoogleMapRef


my_AddressLink
--------------
AddressLinkID
EntityKeyID (this had been called ProfileID)
EntityType (this had been called ReviewType)
AddressID
AddressTypeID
StartDate (date on which the link to the my_Address->'entity' was created)
EndDate (this would default to something like 12/31/2037)

my_AddressType (static(ish) lookup table)
--------------
AddressTypeID
AddressTypeDescription (i.e. VenueAddress, AdminAddress, BookingAddress, PRoffice, etc...)

I know this may look slightly overblown (and we haven't even covered phone numbers) but believe me, once you get this moving
and address details change and/or booking address is different from venue address etc etc, then you'll be glad that you have
a structure that accomodates these nuances. also, you'll want to date-track any changes so as to allow you to retain a
history that is relevant to any given point in time (i hope you get my gist).

That said, :) the structure that you have (for a very simple implementation) would probably be adequate with a few changes
such as adding date tracking on the my_Hotel table (i.e. you'd create a new entry if the address changed and rather than
amend the exisiting record, create a new one. the caveat here being that you've lost the link to all previous reviews etc..

the only other approach with the existing structure would be to 'lose' the old details and over-write with the new ones.

either way is a compromise to the integrity of your data, especially if for example, one of the reviews had complained about
the journey to get to the venue where that venue location had now changed - you see my point!!??)

On reviewing the required changes (as i see them), i amended the structure of the database and the objects to give the
ReviewType and ProfileID more meaningful names.
You can download the app again (with those changes only i.e. only the renaming of the existing
properties/methodnames/database column names - NOT the de-normalised version alluded to above) and look thro it.
I feel it makes it all a bit more readable and 'suggestive' of it's purpose.

http://www.originaltalent.com/downlo...velReview2.zip

anyway, if you have thoughts re the above, let me know and I'll have a sideways think again.


jimi

http://www.originaltalent.com
  #10 (permalink)  
Old May 8th, 2008, 03:39 PM
Authorized User
 
Join Date: Mar 2008
Location: New York, NY, USA.
Posts: 62
Thanks: 1
Thanked 3 Times in 3 Posts
Send a message via Yahoo to kalel_4444
Default

Actually, the tables are virtually completely denormalized. :( The hotel and restaurant data comes from another source and I get updates periodically, and with a lazy mind I figured the less I normailize the easier it would be to update them.

Well, while that's true, I came to a realization last night while standing in front of a 6 ft whiteboard and what is now a dried up expo pen (I know... use a model designer, just used to working with a team and this way we all can see the network and collaborate together). Sorry, I'm rambling, I haven't slept yet, it's 1:30pm now, and I just loaded up on a triple espresso.

Anyways, there is no way I can rest on my laurels. I have to not only normalize it and create an update procedure for multiple tables when I get updates, but create a "parent" table. This came apparent when normalizing the Address table, and btw yes, it can be shared across multiple departments ("places"). e.g. Hotel, Venue, Restaurant, and Show all can have the same address (Las Vegas hotels made this very clear, with everything all-in-one).

Table naming not set yet, but here's hierarchical view of the new model from the root/parent table:

my_Parent
----------------
ParentID (PK)
CategoryID (FK) --> to (Category table, much like enum class in test app)
ProfileID(FK) --> to (Hotel, Restaurant tables / HotelID, RestaurantID, etc)
AddressID (FK) --> to (Address table / AddressID)
ProfileName (Name of Child/Profile, being hotel name, restaurant name, etc)
other identical fields...

my_Hotels/my_Restaurants/my_Shows/my_etc..
----------------
HotelID/RestaurantID/ShowID/etc... (PK)
other proprietary fields...

It gets much deeper, but that's the idea. Additionally, this way I can just do:

my_Reviews
----------------
ReviewID (PK)
ParentID (FK)
other fields...

without having to create composite keys for this and other similiarly associated tables whenever possible.

As for Addresses, I'll be updating vs adding if it changes. I just need a table to bind ParentID's together (restaurant in hotel, show in hotel). I think your my_AddressLink, my_AddressType tables will steer my in the right direction.

Once again, I appreciate all you attention and assistance on this,
Ronnie

 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
list foreign keys in sql 2000 jomet SQL Server 2000 2 May 1st, 2008 11:15 PM
Foreign key not updating with Primary key xavier1945 BOOK: Access 2003 VBA Programmer's Reference 2 July 4th, 2007 09:48 PM
inserting records with foreign and primary keys tdaustin Classic ASP Basics 0 October 4th, 2005 12:49 AM
FOREIGN KEY and PRIMARY KEY Constraints junemo Oracle 10 June 15th, 2004 01:00 AM



All times are GMT -4. The time now is 12:16 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.