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 |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2004-12-09 : 09:20:46
|
| Hi there,I want a user to be able to create a multitude of photo albums. Each album references some images.So I could do this...Users=====userID int pkImages======imageID int pkAlbums======albumID int pkuserID intAlbumDetails============albumID int pkimageID int pkNow I'd like to be able to get rid of the Albums table like so...Users=====userID int pkImages======imageID int pkAlbumDetails============albumID int pkuserID int pkimageID int pkSo the question is, if I want to add a new album for a user, how do I come up with a new album ID? In the first example its easy - I can just have an identity column. But in the second design, the albumID will need to be repeated for as many images as there are in it so an identity column wouldn't work.In the asp.n et IBuySpy store, this problem has been solved for shopping cart entries using a GUID identifier. I was just wondering if there was a way of doing this that allowed me to continue to use simple integers.Cheers,XF. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-09 : 09:28:50
|
quote:
Now I'd like to be able to get rid of the Albums table like so...
No!!! Why would you want to do this!? dont you see this is causing the exact problem you are describing? Where would you put the name of the album, the creation date, or other attributes completely dependent on the album itself.... in the Albums table.- Jeff |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2004-12-09 : 10:00:18
|
| Yes I see that. So why didn't they bother with a 'ShoppingCarts' table in the sample site IBuySpyStore?Do you think there's ever a situation where there's no need for a seperate table in order to group entities? I suppose you'd have to know that you're never going to have attributes dependent on the group itself. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-09 : 10:55:43
|
| Well, it's a demo/sample product for one, it's meant to show what the product CAN do. Doesn't mean it's the best way to do it. Although I would agree that if the shopping cart contents can be stored in a session or cookie, there's not much point in putting it in the database. If they finally order, THEN move the cart items into the SALES ORDER in the DB.For instance, why bother storing cart details if the user never actually orders? (this happens A LOT) If a grocery shopper fills a cart but leaves the store without buying it, should the store keep it until the shopper comes back? What if they never come back? Likewise, as far as modeling entities, most of the time common sense will dictate how to design things. As above, it helps to think about actual physical items, if your data could be based upon such a thing. A photo album is its own thing: it could belong to one person, or no one, or it could be shared or transferred, but none of those factors will change its title or the pictures within it. In that case there's no point in keeping user ID's in the AlbumDetails table; the original design makes sense and should be maintained. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2004-12-09 : 11:07:52
|
| One benifit of putting shopping cart items into the database is that you have referential integrity between the items in the cart and the itmes in the catalog. What if the user places an order and then discovers that the database has no record of the items they're trying to buy?The shopping cart items can be deleted if the user doesn't come back after a few months. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-09 : 11:15:19
|
| [qutoe]One benifit of putting shopping cart items into the database is that you have referential integrity between the items in the cart and the itmes in the catalog[/quote]That's true, but also superfluous: they're filling the cart from that same catalog, how can they pick an item that's not in there? It can also backfire if an item gets deleted from the catalog; you'd have to cascade the delete to the stored shopping cart. Now the customer would come back and find their cart items are gone. The alternative is not store the cart, and if the above situation occurs it can be caught when the order is being placed, and the customer notified the item is no longer available.[qutoe]The shopping cart items can be deleted if the user doesn't come back after a few months.[/quote]True, but a cookie can do the same thing, although it would only be on one computer. |
|
|
|
|
|
|
|
|
Generating a unique identifier