I'm doing a project for university. I'm aiming to create a game trading website that uses the Geolocation API to help users find people to trade with in their local area.
I'm at the stage where I need to build the database. I've provided a link to a screenshot of what I've come up with so far. I'm not too great with databases and had a few questions so I thought I may find some help here.
Some information on the design so far:
Each user creates an account at sign up. Once they have created their account, they can then go to their account page and begin adding their own games that they wish to their personal game catalogue/library. The user will have the ability to search for games that other users have up for trade, if they see a game that they want, they can then make an offer to that user by selecting a game from their own library that they wish to trade with the other user. The other will then receive a notification saying that someone has made an offer to them, at this point, they can review the offer and decide if they wish to accept the trade or decline it. If they accept, then both users will be notified and they will set up a meeting point. Sorry if this is confusing, I'm not very articulate.
1. I'm aiming to keep the "Trade" and "Offer" tables separate, as a "Trade" record will only be created for an offer if the status of the offer is set to "Accepted", so should this be a 1-to-1 relationship? Does anyone know how to create a 1-to-1 relationship in SQL Server?
2. For "Offer" I have multiple foreign keys: 2 from Account and 2 from Game - is this acceptable?
3. General feedback on the design
I really appreciate any feedback and I apologise if this doesn't make sense, I'm not that great at writing.
Account: I would not use Username as the Primary key in a table, use an INTEGER (or BIGINT) id or AccountId or ...
I see no reason for the PlatformDetails table. The user could indicate that his game is suited for the PS3, XBox or ... by selecting a record from Platforms, possibly presented to him in a select list.
The PlatformDetails on itself is not a bad design element, but then it should be taken further than in the current data model. An offer would then reference a PlatformDetails (actually, a game for platform X).
An Offer is a junction table between two Game records. One to the WantedGame and one to the OfferedGame. There is no need to provide extra Foreign Keys to the owner of the WantedGame and to the owner of the OfferedGame, as both owners can be found through the Foreign Key to the owner of those games.
I would probably also integrate Trade into Offer, as Blindman suggested.
A) In your current data model, a user can only have one longitude/latitude point. Suppose I live in village A and commute to city B, I may want to provide two, or more longitude/latitude points.
B) In your current data model, a game must be traded for exactly one other game. Suppose I want to trade ten of my games for your vintage Tetris game, how would you have to change the data model to accommodate this?
I don't say you have to program these variations, but think about how that would influence your data model. I don't know if you will have to discuss your program once it's finished with a teacher. But if I were your teacher, I'd probably ask you these questions.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages