StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODatabase structuring issue in SQL Server; Contests and Registrants
text
Body
copied!<p><strong>Preamble:</strong><br> I'm creating some tables in SQL Server that will be accessed via Linq-to-entities from an MVC application, although the fact that it's SQL Server is probably the only relevant part. I'm not very experienced with databases, and some issues have come up during my implementation that raised some red flags for me. I'm reaching out for some help identifying where I've gone wrong and, hopefully, some suggestions on how to improve it.</p> <p><strong>Business Logic:</strong><br> Anyway, the gist of the business logic is that I have Contests and Registrants. A registrant may only register once for each given contest enforced by email address matching. They may register for multiple contests, but it turns out to be different registrations, logically, because it's a business requirement that the name/email address combo for each registration be kept separate... meaning it's a new pair for every contest. Normally I would use a cross reference table to link registrants to contests... but given the above constraint a xref table wouldn't save me anything that I can see.</p> <p><strong>Data Logic:</strong><br> Contests need to know some data about themselves like Title, start date, end date, etc. and the Winner of the contest, which is of course nullable.</p> <p>Registrants are a name, an email address, and which contest they are registered to. </p> <p><strong>Tables:</strong><br> I don't know how best to communicate my table structure here and Posting complete MSSQL create queries seemed a little too verbose. Please forgive me if you don't like my way below...</p> <pre> Contests { id [int] [non-null] [primary key] winner [int] [nullable] [foreign key into Registrants table] ... some data columns that shouldn't be relevant, title, details, dates, etc ... } Registrants { id [int] [non-null] [primary key] contest [int] [non-null] [foreign key into Contests table] name [nvarchar(80)] [non-null] email [nvarchar(80)] [non-null] } </pre> <p><strong>Concerns and Issues:</strong><br> When I loaded this schema into Entity Framework, I discovered that there is a sort of circular reference. A Contest points (potentially) to a Registrant which points back to that contest. It seems like this is going to be a nuisance if I try to delete records (although that will likely never happen). <strong><em>Is this a potential pitfall, and if so, how do I avoid it?</em></strong> I didn't want to put a 'winner' flag on the registration, since I'd end up with a column that was almost entirely nulls... seems wasteful.</p> <p>Since the unique business constraint on Registrations causes the Contests to Registrations relationship to be One-to-Many, <strong><em>is it acceptable to forego the cross reference table?</em></strong> Or is this bad practice? ( I know it will WORK in my scenario... but it still might be bad form...)</p> <p>In general, it feels like my solution leaves something to be desired, but I possibly (probably) don't have the experience to see the issues. <strong><em>Other suggestions are eagerly sought.</em></strong></p> <p><strong>Close:</strong><br> I know these questions are somewhat vague... and I apologize. It's difficult to nail down exactly why my schema feels <strong>wrong</strong> somehow... that's why I'm seeking your advice.</p> <p>Thanks,<br> Dave</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload