There is a user who has a number of properties, not more than 40-ka. How best to organize data storage in the database and arrange the indexes if you often have to display information about the number of users who have a particular property. For example:

пользователи: умеют плавать(10) умеют прыгать(90) умеют ползать(11)

ps caching is not considered at all.

    2 answers 2

    May suit the method that I used to organize the filters of the goods. In your case, it's like this:

    1. User table
    2. Table with property names
    3. The table in which the user id and property id are stored

    In the last table there may be as many lines of "user_id <=> property_id". Accordingly, if there is any property, then it is easy to get the number of users who have this property

    • Now that's how it is organized. so I want to know what other options are used for such tasks and compare the pros minuses - FLK
    • I tried other options, but somehow everything worked out in an Indian way. After several trials, I stopped at this. It works pretty fast. Sampling the quantity for a certain property took 0.0003 seconds with 10k records in the table - Deonis

    The most intelligible and scalable way is what @Deonis suggested. Its main advantage is that the same privilege setting can be organized for user roles, and then select the total properties of the user based on the properties of the roles set for him by a single request.

    You can also write privileges in BLOB . If we consider the option when each property has its own sequence number, then a set of such properties can be stored in a binary sequence, where each set bit is a property allowed by the user. It is possible to get the flag of the set property through a bit shift. A common set of properties for a user and his roles can be obtained through bitwise addition. Disadvantage: it is inconvenient and bearable to edit all this directly through the database.

    Also, in the case of PHP, you can use the TEXT field. Generate a property array in PHP and write the result serialize() this array to TEXT . Accordingly, when reading, restore the array through unserialize() . Disadvantage: to summarize the allowed properties for users and roles at the query level to the database will not work.

    • if you store properties in a binary sequence, and choose the number of users with a certain property, in select select by bitwise shift, this is an additional operation, and for each property, to get the number, you will need to write a separate query, while with three tables you can choose one query several properties and through count (*) - FLK
    • If stored in bitmaps, then SQL itself is not needed - karmadro4
    • @FLK, the point is to select the entire sequence with a SQL query, and deliver the result at the PHP level to an array. In all cases, you will read all the properties in one request. To make on demand for each property - unjustified expenses. But, by the way, this is just one of the options for storing properties in the database. Even so, a more convenient option offered to you by @Deonis. - KiTE