Database structuring issue in SQL Server; Contests and Registrants

Preamble:

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.

Business Logic:

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.

Data Logic:

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.

Registrants are a name, an email address, and which contest they are registered to.

Tables:

I don't know how best to communicate my table structure here and Posting complete MSSQL 开发者_Python百科create queries seemed a little too verbose. Please forgive me if you don't like my way below...

    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]
    }

Concerns and Issues:

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). Is this a potential pitfall, and if so, how do I avoid it? 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.

Since the unique business constraint on Registrations causes the Contests to Registrations relationship to be One-to-Many, is it acceptable to forego the cross reference table? Or is this bad practice? ( I know it will WORK in my scenario... but it still might be bad form...)

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. Other suggestions are eagerly sought.

Close:

I know these questions are somewhat vague... and I apologize. It's difficult to nail down exactly why my schema feels wrong somehow... that's why I'm seeking your advice.

Thanks,

Dave

On the first crack, you design the database properly qua database, i.e. irrespective of client or middleware issues.

Person
id
name


Contest 
id
name

ContestRegistrations
personid foreign key references Person(id)
contestid foreign key references Contest(id)

Composite Primary key: (personid, contestid)

or you could do this:

ContestRegistrations

id [primary key auto-increment]
personid   foreign key references Person(id)
contestid  foreign key references Coontest(id)

Alternate Unique Key (i.e. unique composite index) on (personid, contestid)

Winner is a separate issue:

Contest
.
.
.
winnerid  foreign key references Person(id)

But this leaves open the possibility that someone could enter a winner who had not registered. That the winner must be a registrant could be enforced procedurally, of course. Enforcing it in the db structure itself using declarative referential integrity is also possible:

Contest
.
.
.
winnerid

foreign key(winnerid, id) references ContestRegistrants(personid, contestid)

Your approach won't work here, because it only looks to see if the winner has registered for some contest, not necessarily the contest in question.