I've three database tables:

  • customers
  • emails
  • invites

Emails are associated with customers with a user_id area.

Invites will also be associated with customers with a user_id area

Emails could be produced with no invitation, but every invitation should have an e-mail.

I must link your email and invites tables so you'll be able to discover the email for the invitation.

However this produces a circular reference, both an invite as well as an email record contain the id for the similar user.

Is bad design and when so, how could I improve it?

My feeling is the fact that with utilization of foreign secrets and good business logic, it's fine.




This isn't a circular reference.

It might be if emails might have a powerful integrity relationship to invites and invites a completely independent strong integrity relationship to emails (for instance).

EDIT: concerning the design

As Hank Holterman highlights now you ask , in case your design is normalized to preferred extent.

Assiming tables: primary secrets for example

customers: id
emails: id, customers_id
invites: id, customers_id, emails_id

and presuming foreign secrets on table_id fields and that not one other constraints are put around the tables (such for instance only part of a vital being unique) then you've modelled the next:

  • for every user there might be several e-mails and you will not have access to emails without any corresponding user record
  • for every email there might be several invites and you will not have access to invites without any corresponding e-mail nor user record (note: in the above definition we're not able to determine if the consumer_id describes entry in emails or perhaps in customers)

Now solve these questions . say if individuals rules match that relating to the real life situation that you're attempting to model.

One method to consider the database design is - there's really no wrong database design, you are able to more often than not find data that will make something which appears like a mistake justified. This is exactly why if you don't take both rules (in type of sentences) and also the tables (E-R diagram, description of tables and associations) it's impossible to express if there's an issue in design (though you'll be able to give suggestions from general observations).

As one example of - the above mentioned note that it's not obvious which table user_id describes might appear simple to answer. And also the common answer, thinking about that you simply stated that each invitation includes a mail, is it should make reference to user_id in the mail table.

Otherwise there may exist an invite that user_id documented on the invitation and also the user_id recorded for any mail will vary.

Normally, this will create a red-colored light labelled 'normalize your data' go flashing in your thoughts. But, frequently unspoken assumption here would be that the email_id determines the consumeridentification, which is probably not true(!).

This is dependent around the semantics of the data (the predicate of every table) - for instance if you're attempting to model a scenario where you'll be able to send invitation to 1 person, and get an e-mail reply from someone else (for instance inviting people through secretary and receiving direct replies), then your red-colored light switches off and all sorts of is okay - that's what really happened which is exactly what you will permit inside your design.

I believe the correct normal form here is always to let Invitation possess a FK regards to Email and not to User. And i believe that will work fine.

Inside your description you condition that the Invitation goes to some User but in the constraints it's better to express that the Invitation goes for an Email.

However, you're right, giving Invitation both an UserId as well as an EmailId wouldn't be an excellent problem. It wouldn't give much advantage either.