Can someone help me sort something out. Suppose we have two tables in a database.
One is named Person, and one is named Birthday. Is this a many to many relationship
or a 1 to many relationship?
Person has many birthdays. Example. Joe Schmoe has a birthday for every year of his life.
birthday(7/11/1976) has many People (Many people have a birthday on 7/11/1976.
My first question would be:
why are you creating a table just for birthdays, when you can just add a field to the Person table?
|||This is just a hypothetical question. Probably not the best one in the world but one I believe will help me to understand. So if someone can help me out I would appreciate it.
|||Hi,
these articles will help you get familiar with Entity Rlationship Model
http://en.wikipedia.org/wiki/Many-to-many
http://en.wikipedia.org/wiki/Entity-Relationship_Model
I hope this helps.
|||A hypothetical question or a philosophical one? Well, those wikipedia articles are a bit dry so let me explain with a different example.
ASP.Net 2.0 membership gives you the concepts of a User (person) and a Role. A User can be in many Roles - the same login can be assigned to the Administrator role, the Sales role, the Helpdesk role etc. A Role can have many users. This is a many-to-many relationship.
To model this, we have a Users table and a Roles table. And key to the many-to-many relationship is a table "in the middle", containing the User ID and the Role ID. This allows the same User to be in the connecting table many times with different roles. And the same role to be in the connecting table with many Users.
Hope that helps!
|||If we are going to talk about database design, then your database has to be normalized (Idea: google it).
The fast solution which is also normalized is to have a column in the "Users" table for the BirthDay.
But if you still beileve for the example you wrote in the last line:
AppDevForMe:
Person has many birthdays. Example. Joe Schmoe has a birthday for every year of his life.
birthday(7/11/1976) has many People (Many people have a birthday on 7/11/1976.
If we supposed this is the case (which is a very bad design), then the relation is Many-to-Many and here is how to deal with this relation.
You have to create a new table (namedAccessor table in database world), which will take the primary key of each of the two tables ... those primary keys will be forign key in the newly created table (the accessor table) and they will form the priamey key for it (it called a composite primary key in database world).
Now, the relation become One-to-Many for each of the old two tables with the newly created table (Accessor).
I suggest you to read about: ERD, database design, database relations, normalization (must), denormalization (good some time to increase the perfomance by making number of joining with tables less).
Hope this will help you a alot.
|||If we are going to talk about database design, then your database has to be normalized (Idea: google it).
The fast solution which is also normalized is to have a column in the "Users" table for the BirthDay.
But if you still beileve for the example you wrote in the last line:
Person has many birthdays. Example. Joe Schmoe has a birthday for every year of his life.
birthday(7/11/1976) has many People (Many people have a birthday on 7/11/1976.
If we supposed this is the case (which is a very bad design), then the relation is Many-to-Many and here is how to deal with this relation.
You have to create a new table (namedAccessor table in database world), which will take the primary key of each of the two tables ... those primary keys will be forign key in the newly created table (the accessor table) and they will form the priamey key for it (it called a composite primary key in database world).
Now, the relation become One-to-Many for each of the old two tables with the newly created table (Accessor).
I suggest you to read about: ERD, database design, database relations, normalization (must), denormalization (good some time to increase the perfomance by making number of joining with tables less).
Hope this will help you a alot.
sql
No comments:
Post a Comment