Geek asks and answers

PHP - saving multiple attributes in the most efficient way

Just wondering, can someone point me in the right direction to store multiple attributes and their values ​​in SQL?

Say I have a first name, first name, last name, company, Twitter, Facebook, etc. with a lot of attributes that may be added in the future.

There are several methods that have crossed my mind, one is to save all the answers in one column, all the answers in another, as a result of decoupling and storing them as the name Firstname ||| Surname ||| Company etc. Or do I have much experience with them other than bitwise operators?

Just looking for the best / most effective solution is valid if someone can give me an idea and perhaps an example that would be great.

Cheers, Christian

+3
source share
3 answers

If what you are looking for is a way to bind variables to a given user, and you don't want to change the table every time a new attribute type exists. Perhaps EAV will help you. This is a tiny bit for a message if you're not used to it. But it gives you a very flexible structure. Read more about it here.

A tiny example:

    Users
    user_id username  paswword email
    1       "katsuke" "abc"    "demo@demo.com"

    User_EAV
    user_id attribute_name value
    1       "First Name"   "Katsuke"
    1       "Last Name"    "Isikashi"
    1       "Library Card" "14124214"
    1       "Dental Card"  "asd123123"

Hope this helps. Good luck

+5
source

Depending on your needs, you can add an attribute table 1: N

id:     integer
name:   string
value:  string

You can also consider adding optional fields as an attribute and adding required fields to the main table. This will allow you to add additional fields later.

+3
source

If you just want to store and retrieve them (this is not a massive update, sort or search on them), you can go with the basic structure to a regular table and extended values ​​in a serialized array (or json). Doctrine has support for this ( column: {additionalattrs: {type: object}})

In fact, if you want to search, json and serialize are still available for search in the clear, but do not count on any indexing and are ready for some false positives.

+3
source

More articles:


All Articles