Relation Theory
-
I am not new but somehow I have really avoided getting too involved in database design. My question is fairly basic. If I have a relationship where one object technically possesses many of another, should I just make a standard many to one relation or should I create a bridge table? I will give an example. Let's say that I am trying to keep track of credit card numbers and their owners which I am not just so I don't get screamed at by the security people. The real example would be too hard to explain but this is comparable. So anyways, should I have a credit card numbers table that will have ID (pri key), CCnumber, and Owner columns? Then I would point the Owner column at the pri key column in the People table right? Does it matter that technically that people own credit cards and then maybe I should be making a bridge table? My hunch is that I don't need a bridge table but since this is going to be a big project, I would rather not find out the hard way after I have over 100 tables.
-
I am not new but somehow I have really avoided getting too involved in database design. My question is fairly basic. If I have a relationship where one object technically possesses many of another, should I just make a standard many to one relation or should I create a bridge table? I will give an example. Let's say that I am trying to keep track of credit card numbers and their owners which I am not just so I don't get screamed at by the security people. The real example would be too hard to explain but this is comparable. So anyways, should I have a credit card numbers table that will have ID (pri key), CCnumber, and Owner columns? Then I would point the Owner column at the pri key column in the People table right? Does it matter that technically that people own credit cards and then maybe I should be making a bridge table? My hunch is that I don't need a bridge table but since this is going to be a big project, I would rather not find out the hard way after I have over 100 tables.
Using your exampleof people and credit cards, you would have a one-to-many relationship. FYI, it is not called a many-to-one. You would take the primay key in the people table and include it as a foreign key in the credit card table. In my experience, you only need a bridge table when you have a many-to-many relationship. A many to many relationship is always broken down to two one-to-many relationships. Consider Students and Courses. A student may take 0,1 or many courses and a course may be taken by 0, 1 or several students. In this case you create an intermediary/relationship/bridge table in which the you have foreign keys from the student and course tables. I would also advice that you start getting involved in database design
-
I am not new but somehow I have really avoided getting too involved in database design. My question is fairly basic. If I have a relationship where one object technically possesses many of another, should I just make a standard many to one relation or should I create a bridge table? I will give an example. Let's say that I am trying to keep track of credit card numbers and their owners which I am not just so I don't get screamed at by the security people. The real example would be too hard to explain but this is comparable. So anyways, should I have a credit card numbers table that will have ID (pri key), CCnumber, and Owner columns? Then I would point the Owner column at the pri key column in the People table right? Does it matter that technically that people own credit cards and then maybe I should be making a bridge table? My hunch is that I don't need a bridge table but since this is going to be a big project, I would rather not find out the hard way after I have over 100 tables.