favorites table creation

[jimfog]

I am developing an application where the user will have the option of having favorites of service providers.. It is a child table with 2 columns, both of them will be foreign keys, the one column is a foreign key from the favorites table to a table that holds credentials(username/password), this will mark the user. The other column is a foreign key to the service providers table, this will mark the service provider, the one that is favorite to the specific user. Here we have a many to many relationship. And the problem comes from the fact that what happens when a user has more that one favorite. That means multiple values to the service provider column of the favorite table-this column is of the INT type. I know that this violates the principle of atomicity of RDBMS-I cannot find an alternative though. What can I do in such a case?Thanks.

[justsomeguy]

That doesn't have anything to do with atomicity. I don't see a problem, that's the way you store a many to many relationship.

[jimfog]

I thought that a column can never have more than a value.

[justsomeguy]

A given field has a single value. A column has many values in many rows. Rows have many values in many columns. I'm not sure what that has to do with atomicity though, an atomic transaction in a database means that if you have a complex transaction with multiple parts, either they all occur or none of them occur. A violation of atomicity happens when you have a complex process where a few of them happen, then you have an error, and the rest do not happen and the ones that did happen do not get rolled back. If the transaction was atomic then if an error happened part way through then the parts that already finished would get rolled back. If you made one of the columns in the table a unique or primary key then that's a problem. The primary key should contain both columns.

[jimfog]

Yes, you are right...the correct definition of atomicity is yours. What I am trying to say is that a given field cannot have more than a value-something which I thought is violated here. If you noticed I said "I thought", cause I found(these last minutes) that in a favorites table like the one I want if forexample a user has 2 favorites, then this would be done this way: user Id serviceprovidersID 1 2 1 3 Assume the above to be the table-is it OK now?

[justsomeguy]

Yeah, that's fine. That's how this works, you have multiple rows where each row lists a user and a service provider. I'm not sure what you mean when you're saying a field cannot have more than one value, it doesn't make sense for anything to have more than one value. The entire concept of something having more than one value doesn't make sense. We're not into quantum computing just yet.

[jimfog]

There is a debate if a quantum computer will ever be build-despite the progress been made. Even then, they can be useful onlyin specific types of problems. It is known for example that factorization can bring TO ITS KNEES even a supercomputer-while this is trivial for humans, since our brain ischaracterized by massive parallelism, a property today's computers do not have, but quantum will.

[Raviteja.Kodati]

U can make credentials along with ISP as primary key.. den u dnt have any problem.

[xhtmlchamps1]

Do not create any child table, you can create a separate table and it should have common 'ID' in both of the tables. Then use join queries For example; inner join, left join, right join etc. Thank you