CODEWITHSUNDEEP
mysql
Joel
Joel

Reputation: 6107

Saving multiple attributes in a single MySQL row

I have a MySQL table , each row representing a Person. For each person I would like to show a list of related links (when generating his page on my website), so I created a column called "RelatedLinks" which there I save raw HTML content such as:

<a href="domain1.com">Related Link 1</a> <a href="domain2.com">Related Link2</a>

Then I just use PHP to echo $row["RelatedLinks"]; within the HTML itself.

The problem with this implementation is that I have little control over the links, for example in changing their order of appearance on the website, deleting/editing a specific link and so on.

The second idea I thought about was to create a column in the table for each related link, such as "RelatedLink1", "RelatedLink2" and then use PHP to echo $row["RelatedLink1"] . "<br />" . $row["RelatedLink2"].

Although I do have much more control with this implementation, I think I am creating a big overhead in the table.

What is an efficient way to achieve this functionality?

EDIT

Referring to Gabriel's answer, the question now is about complexity:

With the current implementation, once I select the person I have all the links in my hand - complexity of O(1). What will be the complexity of running a query to select all foreign keys of the current Person, as suggested by Gabriel (and Haim) below?

Thanks,

Joel

Upvotes: 1

Views: 542

Answers (2)

Gabriel Croitoru
Gabriel Croitoru

Reputation: 133

If you only want to store and display the links, and the edit actions are very few, the first option is the best one. In this case, you will have to save the new values on edit.

If you think you'll have more edit operations, you could try to create a second tabel, to contain id_pearson, link, text, order . And you can use this tabel to edit the links for a specific pearson.

Also, if you need the same link for two or more persons, it's preferable to use 3 tables.

persons table : id_person, name (and all other info)

links table : id_link, text, link

person_links : id_person, id_link, order

Using this setup, you can edit the links in one place, and all persons will have access to the same version.

LATER EDIT IN RESPONSE TO COMMENT

Okey, regarding complexity of operations, nothing could beat the first solution. But you must take in account several things regarding what you need:

  • how often do you expect to change the links ?
  • who is gone change the links (the admin of the site, or the end user) ?
  • how many cases of the same link to appear for different persons (using the first solution, you will have to edit all persons that contain that link )

To answer your question of complexity, the solutions proposed by me are O(n) if I'm not mistaking. Using foreign keys and proper indexing in MySql, you shouldn't have a problem with this.

Summary: if performance is very important to you and the links will be edited only by the admin, use the first solution. If not, I would use the third one.

Hope this helps, Gabriel

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166396

You need a secondary table that contains a PersonID (foreign Key), RelatedLink(ID) and maybe an OrderID.

Something like

Table PersonRelatedLinks
PersonID
RelatedLink or RelatedLinkID
OrderID
SpecialFormat maybe

Upvotes: 1

Related Questions