locked
Help designing a relational database RRS feed

  • Question

  • Hello all, this is my first post to MSDN. I hope one of the clever people on here can help me out. I've been trying to teach myself relational database theory and I don't really want to go too deeply with it - it's only a hobby! Anyway, I've been reading "The Essence of Databases" by F. D. Rolland and trying to create my own database using the "entity/relationship" model described in the book.

    I thought I had a grasp of it and was going well until I came to put my actual database into SQL Server. What I find now is that I have too many foreign keys and I can't fill out rows in my tables because they reference foreign keys in other tables that haven't yet been defined. I'm kind of chasing my tail. It's pretty hard to describe in words, so please have a look at my "design document" here: http://cid-f303e91efe3894cb.office.live.com/self.aspx/.Public/Database%20Design.pdf?lc=2057

    To give you some background, I thought it'd be nice to create a GUI front-end for a game-emulator (I know this has been done many times before). I'm trying to tie multiple files together in a database so that I can look up a game in the database, then find what system (only one system per game), emulators and images are associated with that game. However, I'd also like to be able to look up a system and see what games are related to it. I'm probably hugely over-complicating things with my design! Any help with untangling the mess that I've created would be greatly appreciated.

    Thanks in advance,

    Pete

    Thursday, August 19, 2010 7:05 PM

Answers

  • Pete,

    Relational theory is wierd. You've done a good job of getting your head around it.

    The only thing I'd question is the role of the File table. You're storing the file names as attributes of the entities, so why have you split out the file path into a separate table? It's creating a lot of circular relationships that I suspect are why SQL is not cooperating...

    - Rebecca


    Rebecca M. Riordan
    • Marked as answer by Pete-T Friday, August 20, 2010 3:06 PM
    Friday, August 20, 2010 12:44 PM

All replies

  • Pete,

    Relational theory is wierd. You've done a good job of getting your head around it.

    The only thing I'd question is the role of the File table. You're storing the file names as attributes of the entities, so why have you split out the file path into a separate table? It's creating a lot of circular relationships that I suspect are why SQL is not cooperating...

    - Rebecca


    Rebecca M. Riordan
    • Marked as answer by Pete-T Friday, August 20, 2010 3:06 PM
    Friday, August 20, 2010 12:44 PM
  • Hi Rebecca,

    Thanks for taking a look, I appreciate the help. You've certainly made me think a little more about the problem. I think you're right in that the "Filepath" table is making things overly confusing. I was trying to be clever and reduce duplication because in the final solution there would likely be many files, but the different files would probably only be stored in a handful of directories.

    OK, as I was typing this reply, I went back and looked at the design and how I was trying to populate the database. It seems to be a simple case of me not respecting the relationships in the database. Until data is entered in the "System" table it's not possible to enter any data into the "Game", "Graphic" or "Emulator" tables as they all ultimately have a dependancy on the "System" table. So it does actually work in its current state, but I think having the "Filepath" seperated out into a seperate table will unneccessarily complicate the coding of my application.

    Thanks again for your help, it's much appreciated.

    Pete

    Friday, August 20, 2010 3:06 PM