MMA Database Design Hiccup

[JoshEngleman]

Hi Everyone,



I've been looking to build my own personal database of MMA results for quite some time and finally decided to pull the trigger. I've never really done any database work before, so it is all pretty new to me. I have 95% of the design mapped out but I'm really struggling with one part and I hope that someone here can help me.

As of right now, I have 5 tables. They are: fighters, fights, company, card and method. They are broken down as follows:

fighters: fighterID (autonumber), first name, last name, nickname, date of birth

company: companyID (autonumber), company name

card: cardID (autonumber), card name, companyID

method (ie, KO, Submission, Decision): methodID (autonumber), method

My issue is with the fights table. I'm sure there is a really easy answer to this, but it has been driving me crazy. I know that the primary key is supposed to be a unique value. How can I have each fight have a unique fightID if I need to two people to be represented in that fight?

I need to show that Jim won, Pete lost, along with links to the cardID and methodID. Am I making any sense?

[alansidman]

You will need to have another table that lists each fight and you will need to create a junction table as it has a many to many relationship.

See here

http://www.youtube.com/watch?v=7XstSSyG8fw

[JoshEngleman]

Awesome, thanks. That makes a lot of sense. Now I just need to try and figure out how to enter this data efficiently.