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 |
|
Onyxstorm
Starting Member
2 Posts |
 Posted - 2012-11-08 : 16:36:28
|
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
1974 Posts |
Posted - 2012-11-09 : 03:08:47
|
| 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 |
 |
|
|
Onyxstorm
Starting Member
2 Posts |
Posted - 2012-11-09 : 09:07:08
|
| 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? |
|
|
|
|
|
|
|
|
Database design question - verifying architecture