SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Database design question - verifying architecture
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Onyxstorm
Starting Member

USA
2 Posts

Posted - 11/08/2012 :  16:36:28  Show Profile  Reply with Quote
I am working to learn SQL database programming and have decided to learn by doing. I am building a database that I will use as an inventory program for one of my hobbies, Magic the Gathering.

I am building the database in a way that it will store all of the data for the individual cards as well as my personal inventory and where they are physically located (whether in an album, or a box, etc).

I have most of the design completed, but there is a very important portion of it that just does not feel right.

Here is a map that I made in excel that shows the tables:


The areas in green I am fairly confident on. The area in red is where I may need some help.

I want to track the location of each card in my inventory, however they may not all be located in the same place. I may have 5 'Forest' cards that are in 5 different Locations. This is why I have it split into 2 tables.

Is there a better way to do this? As it is it seems like it would work but I'm interested to see a more experienced person's opinion.

Thanks in advance.

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/09/2012 :  03:08:47  Show Profile  Visit chadmat's Homepage  Reply with Quote
If I understand your question, you are asking if this is the proper way to model a many to many relationship? A card can be in many locations, and a location can contain many cards. So yes, adding the table for that many to many relationship is the proper way to handle it.

If that wasn't what you wer easking, then please reask with more details.

-Chad
Go to Top of Page

Onyxstorm
Starting Member

USA
2 Posts

Posted - 11/09/2012 :  09:07:08  Show Profile  Reply with Quote
Yes, that was my question. I am trying to find out if this method is the best way to do it, or if there is an alternate way that would, for example, force a location for every card (defaulted to locationID 0). I can't think of a way to funnel the data through one table or the other though, because they are both so variable.

Pretty much, here is the issue I am having:

Let's say that I have 5 'Forest' cards, so I create a row in the Inventory table with the placeholder RecordID and the valid CardID, witha 5 in the inventory column. If I add or subtract for the inventory for this card, I would simply update the number in that one column.

However, let's say that I place 4 'Forest' cards into one specific location in the CardLocation table. Then, I move one of them into a different location. Would I then have to update the entire CardLocation table? I'd have to update the existing row and create a new one to reflect the location of the moved card. And beyond that, the two are not directly tied together - ie; what is stopping me from adding more cards to locations than I actually have?

Edited by - Onyxstorm on 11/09/2012 11:30:11
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000