Jump to content

favorites table creation


Recommended Posts

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.

Link to post
Share on other sites

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

Link to post
Share on other sites

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.

Link to post
Share on other sites

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?

Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites
  • 1 month later...
  • 2 weeks later...

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

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...