Association table mapping design pattern in PHP

Stefanescu Mihai 8 months ago Design Patterns

Acest pattern, association table mapping este un mod de a crea o relatie de tip many-to-many intre entitatile unei baze de date. Se foloseste in momentul in care ai 2 entitati intre care trebuie sa existe o relatie de tip many-to-many. In loc sa adaugam mai multe coloane pentru cele 2 entitati, facem o tabela noua care sa se ocupe de aceasta asociere.

Hai sa vedem cum ar putea functiona acest pattern.
- Sa pesupunem ca avem 2 entitati, "users" si "groups", fiecare user poate fi in mai multe grupuri, iar fiecare grup poate avea mai multi useri.
- In loc sa adaugam coloana group_id pe tabela users si o coloana user_id pe tabela de groups, o sa facem o tabela noua numita user_group.
- Tabela user_group va avea 2 campuri, user_id si group_id.
- Fiecare rand din tabela user_group reprezinta o singura asociere intre un user si un grup.
- De exemplu, daca user-ul cu id-ul 1 este un grupul cu id-ul 1 si in grupul cu id-ul 2, in tabela user_group o sa avem 2 inregistrari (1,1) si (1,2)

Acest design pattern iti permite sa creezi, citesti, modifici si stergi usor asocierile many-to-many, de asemenea, poti adauga si extra informatii legate de asociere, cum ar fi data creeri asocierii sau un rol al user-ului in grup.

De asemenea, ce este important de retinut este faptul ca folosind acest pattern vei fi nevoit sa te folosesti de un JOIN in query-ul de SQL pentru a scoate asocierea.

Mai jos am sa explic un exemplu al acestui pattern in PHP folosind PDO (PHP Data Objects) pentru a interactiona cu baza de date MySQL.

Care sunt pasii pe care trebuie sa ii urmam?

1. Facem o tabela noua de asociere a celor 2 entitati, precum user_group cu coloanele user_id si group_id
2. Pentru a adauga o noua asociere facem un insert cu asociere folosindu-ne de ID-urile din cele 2 entitati

$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'password'); $stmt = $pdo->prepare("INSERT INTO user_group (user_id, group_id) VALUES (:user_id, :group_id)"); $stmt->execute(['user_id' => 1, 'group_id' => 2]);

3. Pentru a prelua toate asocierile unui anumit user sau unui anumit grup trebuie sa facem un select pe tabela de asociere cu JOIN pe tabela de asociere

$stmt = $pdo->prepare("SELECT * FROM user_group JOIN groups ON user_group.group_id = groups.id WHERE user_id = :user_id"); $stmt->execute(['user_id' => 1]); $groups = $stmt->fetchAll();

4. Pentru a sterge o asociere, trebuie sa stergi inregistrarea din tabela de asocieri ce corespunde datelor dorite, atat din prima tabela, cat si din a doua.

$stmt = $pdo->prepare("DELETE FROM user_group WHERE user_id = :user_id AND group_id = :group_id"); $stmt->execute(['user_id' => 1, 'group_id' => 2]);

Acest exemplu al design pattern-ului association table mapping este unul simplist, el poate fi modificat conform necesitatii voastre de implementare.

Programator de ~8 ani, am lucrat la proiecte din mai multe industrstrii, de la eCommerce la telecomunicatii la automatizari. In acest timp am folosi diferite tehnologii, de la PHP, MySQL, PostgreSql, RabbitMq, Redis, Memcached si altele.


Get in touch
Pentru nelamuriri, dubii, comentarii si chestii de pe suflet ne putem auzi pe Discord, Reddit sau poti deschide o discutie noua pe forum

You'll be at the moment: Are you struggling to find safe and effective hormonal # 5 months ago I absolutely love your website.. Pleasant colors & theme. Did you develop this web site yourself? Please reply back as I'm hoping to create my very own website and want to find out where you got this from or exactly what the theme is named. Kudos!
Beverly # 3 months ago Hello I wanted to reach out and let you know about our new dog harness. It's really easy to put on and take off - in just 2 seconds - and it's personalized for each dog. Plus, we offer a lifetime warranty so you can be sure your pet is always safe and stylish. We've had a lot of success with it so far and I think your dog would love it. Get yours today with 50% OFF: https://caredogbest.shop FREE Shipping - TODAY ONLY! Have a great time, Beverly
Greg # 3 months ago Morning I wanted to reach out and let you know about our new dog harness. It's really easy to put on and take off - in just 2 seconds - and it's personalized for each dog. Plus, we offer a lifetime warranty so you can be sure your pet is always safe and stylish. We've had a lot of success with it so far and I think your dog would love it. Get yours today with 50% OFF: https://caredogbest.shop FREE Shipping - TODAY ONLY! All the best, Greg
Mario # 3 months ago Greetings If you ever need Negative SEO or a de-rank strategy, you can hire us here https://www.speed-seo.net/product/negative-seo-service/
Josefina # 3 months ago Hey Looking to improve your posture and live a healthier life? Our Medico Postura™ Body Posture Corrector is here to help! Experience instant posture improvement with Medico Postura™. This easy-to-use device can be worn anywhere, anytime – at home, work, or even while you sleep. Made from lightweight, breathable fabric, it ensures comfort all day long. Grab it today at a fantastic 60% OFF: https://medicopostura.com Plus, enjoy FREE shipping for today only! Don't miss out on this amazing deal. Get yours now and start transforming your posture! Cheers, Josefina
Shani # 2 months ago Hi there Is your dog's nails getting too long? If you're tired of going to the vet or groomer to get them trimmed, why not try PawSafer™? With PawSafer™, you can trim your dog's nails from the comfort of your own home, and it only takes a few minutes! PawSafer™ is the safest and most convenient way to trim your dog's nails, and it's very affordable. Get it while it's still 50% OFF + FREE Shipping Buy here: https://pawsafer.sale All the best, Shani
Gerald # 2 months ago New Multifunction Waterproof Backpack The best ever SUPER Backpack: Drop-proof/Scratch-resistant/USB Charging/Large capacity storage 50% OFF for the next 24 Hours ONLY + FREE Worldwide Shipping for a LIMITED time Buy now: https://thebackpack.co The Best, Gerald
Isobel # 1 month ago World's Best Neck Massager Get it Now 50% OFF + Free Shipping! Wellness Enthusiasts! There has never been a better time to take care of your neck pain! Our clinical-grade TENS technology will ensure you have neck relief in as little as 20 minutes. Get Yours: https://hineck.co Thank You, Isobel Association table mapping design pattern in PHP - Invata-Programare
Porfirio # 1 month ago Get The Worlds Greatest Magic Sand Free Beach Mat! Watch sand, dirt & dust disappear right before your eyes! It's perfect for beach, picnic, camping or hiking. Act Now And Receive A Special Discount For Our Magic Mat! Get Yours Here: https://magicmats.co Best Wishes, Porfirio
Eloise # 1 month ago Good day, I hope this email finds you well. I wanted to let you know about our new BANGE backpacks and sling bags that just released. Bange is perfect for students, professionals and travelers. The backpacks and sling bags feature a built-in USB charging port, making it easy to charge your devices on the go. Also they are waterproof and anti-theft design, making it ideal for carrying your valuables. Both bags are made of durable and high-quality materials, and are perfect for everyday use or travel. Order yours now at 50% OFF with FREE Shipping: http://bangeshop.com To your success, Eloise
Lwwynx # 1 month ago tricor tablet <a href="https://fenoficor.com/">purchase fenofibrate online</a> order tricor 160mg generic
Spencer # 1 month ago Hi Looking to improve your posture and live a healthier life? Our Medico Postura™ Body Posture Corrector is here to help! Experience instant posture improvement with Medico Postura™. This easy-to-use device can be worn anywhere, anytime – at home, work, or even while you sleep. Made from lightweight, breathable fabric, it ensures comfort all day long. Grab it today at a fantastic 60% OFF: https://medicopostura.com Plus, enjoy FREE shipping for today only! Don't miss out on this amazing deal. Get yours now and start transforming your posture! To your success, Spencer
Lwpzuq # 1 month ago zaditor 1mg generic <a href="https://geozipin.com/">order geodon 40mg</a> tofranil where to buy
Jtpcqq # 1 month ago minoxidil price <a href="https://edxgetonline.shop/">non prescription ed drugs</a> online ed pills
Zzeogv # 1 month ago cost precose 50mg <a href="https://dipyxcin.com/">purchase fulvicin pills</a> buy griseofulvin generic
Igcpuo # 1 month ago buy aspirin pills <a href="https://levquinon.com/">purchase lquin generic</a> buy zovirax online cheap
Zzyaqd # 3 weeks ago dydrogesterone for sale online <a href="https://janozin.com/">purchase sitagliptin online</a> buy jardiance 10mg for sale
Wizzqj # 3 weeks ago order florinef 100mcg generic <a href="https://acipolax.com/">buy generic rabeprazole over the counter</a> imodium 2mg sale
Rxvbuv # 3 weeks ago order monograph 600mg <a href="https://colotazol.com/">mebeverine 135mg sale</a> pletal 100 mg generic
Mxsgxn # 2 weeks ago purchase prasugrel generic <a href="https://imocarbam.shop/">prasugrel 10mg canada</a> buy tolterodine cheap
Oayird # 2 weeks ago mestinon 60mg oral <a href="https://mestien.com/">buy generic mestinon for sale</a> rizatriptan generic
Tekits # 2 weeks ago order ferrous sulfate online <a href="https://betaotec.com/">sotalol 40 mg drug</a> buy sotalol 40 mg generic
Nidia # 2 weeks ago Good day, If you are one of the sufferers of the common problems nails have, then you are in luck! Our Toenail Clippers is here to help. It has a specially designed clip that can help those with troubles with winding nails, hard nails, two nails, nail cracks, deep nails, and thickened nails. We are confident that our Toenail Clippers will provide you with the results you are looking for. Get yours today with 60% OFF: https://podiatristusa.sale Best Wishes, Nidia
Fmvssf # 1 week ago generic zovirax <a href="https://aelimax.shop/">buy generic xalatan</a> exelon 6mg cost
Dqgbfw # 1 week ago buy betahistine 16 mg generic <a href="https://haloatan.com/">haloperidol medication</a> order benemid online cheap
Jonah # 1 week ago Hi there I wanted to reach out and let you know about our new dog harness. It's really easy to put on and take off - in just 2 seconds - and it's personalized for each dog. Plus, we offer a lifetime warranty so you can be sure your pet is always safe and stylish. We've had a lot of success with it so far and I think your dog would love it. Get yours today with 50% OFF: https://caredogbest.com FREE Shipping - TODAY ONLY! Best Wishes, Jonah
Mrellw # 1 week ago micardis 20mg price <a href="https://molnenil.shop/">molnunat 200mg for sale</a> buy molnupiravir 200 mg without prescription
Qfwmcb # 6 days ago oral cenforce 100mg <a href="https://enapocen.shop/">buy naproxen cheap</a> buy cheap generic aralen
Elton # 6 days ago Get The Worlds Greatest Magic Sand Free Beach Mat! Watch sand, dirt & dust disappear right before your eyes! It's perfect for beach, picnic, camping or hiking. Act Now And Receive A Special Discount For Our Magic Mat! Get Yours Here: https://magicmats.co Many Thanks, Elton
Mario # 4 days ago Greetings If you ever need Negative SEO or a de-rank strategy, you can hire us here https://www.speed-seo.net/product/negative-seo-service/
Bwuisf # 4 days ago modafinil online <a href="https://pronlinenil.shop/">how to get phenergan without a prescription</a> order deltasone 5mg for sale
Club-ul este dedicat membrilor si ofera access la mai multe zone ale website-ului.
Login Register

🔖 Bookmarks
✨ Pentru a sustine aceasta comunitate am sa te rog sa te autentifici sau sa te inregistrezi!

🌪️ Discord
Back to top
Folosim cookie-uri pentru a oferi functionalitatile critice ale aplicatiei Invata-Programare. Folosim cookie-uri si pentru a analiza traficul, pentru care e nevoie de consimtamantul dvs. explicit.