Re: business users services

[Rick James]

> If for example the above b_user has an ID of 10,this will appear 3 times.
> How do you comment this from an efficiency standpoint?Related to the DB of course
> Is there a better alternative you think?

3 copies of "10" is to be expected. That is more efficient than the alternative -- 3 copies of all the data associated with "10".

> two or more service_IDs being different but corresponding to the same service

Same argument for that direction.

A goal of "good database design" is to minimize redundancy. The 3 copies of "10" is better than 3 copies of what it represents. Anyway the 3 copies are participating in 3 different "relationships", hence 'necessary'.

Now let's do some numbers... Let's say you have 20K different users providing 5K different services, such that there are 40K 'relationships'. (The average user provides 2 services and the average service is provided by 8 users.) The record (table row) for a user might be about 200 bytes. Ditto for a service record. Let's say the many-to-many relationship table will have 80K rows of about 20 bytes per row (2 4-byte INTs, plus overhead).
* 20K * 200B = 4MB
* 5K * 200B = 1MB
* 40K * 20B = 800KB (or 600KB if using MEDIUMINT UNSIGNED)

The total is about 5.8MB. (This is 'small' by todays standards.)
There is no redundant information in the Entity tables (Users and Services).
Only 14% (or 11%) of the total disk space is for the relationships.

(See a discussion of "normalization" on how to deal with redundancy that does exist in the Entity tables -- such as multiple Users living in the same city.)