December 12, 2022 at 12:21 am
I have been away from SQL and programming for 9 years due to a severe illness. An old client asked me to do him a favor by designing an application for him. I can't seem to come up with a clean database design. I don't know if it's because I am rusty or because he has an odd requirement.
Here are the givens:
This customer runs several pickleball locations that are NOT associated with each other because they want to keep each location as a completely separate and autonomous entity.
Each location has several members. So naturally I thought of a one to many relationship between the pickleball location and member as each location has multiple members, and a member can only belong to one location.
In order to accomplish this when a member signs up the member registers for a specific location. As part of the registration process, the member must supply an email address. The email address is an important part of my question.
Here's where it gets confusing.
Even though members belong to one location they want to allow members to play at multiple locations. When a member wants to schedule time to play they have to log into the system and select the location they want to play at. This isn't a problem because the member registers at each address they play at.
For the purpose of logging in, they want the member to use his/her email address as the username. Here's where it gets tricky. They don't want to require the member to have a different email address for each location.
With the one to many relationship that I described earlier that would mean that the login process would have to scan the entire members table for that email address in order to find out which locations this member has registered for in order to present a list of locations for the member to select from. To further complicate things, the email address has to be unique within a location.
I am not sure how to proceed. Do I turn the one to many relationship that I described earlier into a many to many relationship? Except for the login process the one to many relationship that makes perfect sense. If I change this to a many to many relationship should I create an junction table with the LocationID, MemberID and the email address? Are there better solutions?
Any advice would be much appreciated.
I apologize for presenting such a long winded explanation.
Thanks in advance for any help that I can get.
December 12, 2022 at 8:28 am
I assume you will design a registration table (MemberId, LocationId, DateRegistered, etc), with FKs to Member and Location? If so, add a Boolean 'IsPrimary' to that table to denote the member's prime location.
Getting the locations at login time becomes straightforward after this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 12, 2022 at 3:02 pm
Each location can't be completely separate if email addresses must be unique. You need to replicate the email addresses across all tables, or have a "master" email table that contains emails for all locations.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 12, 2022 at 8:30 pm
Thank you both for getting back to me.
I am glad that my explanation wasn't as confusing as the way the customer explained things to me.
Phil's response is very much in line with what I came up with. The rust from my 9 year layoff is clearly showing.
I was considering an idea similar to Scott's idea until the customer made it clear that the email field is only going to be used during registration for the purposes of logging in. Just to be safe in case the customer changes his mind about the email address, I am going to create a Member's Contact Table that contains any email address that the member wants to receive email at.
Again, thank you both.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply