CODEWITHSUNDEEP
mysqldatabasedatabase-designpolymorphic-associationserd
Udders
Udders

Reputation: 6986

mysql relationships, make them optional?

Hello I have the following mysql relationship,

enter image description here

As you can see the companies table has a 1:n relationship withe the members table. However this seems to implicit, meaning that when I try and save a member to my database they have to be associated with a company, or they insert does not happen.

Basically the architecture of our user signup process is that you can be an individual or sign as a company. How do I make my database design reflect this, so the relationship between the two tables is not a must.

Upvotes: 1

Views: 1422

Answers (2)

Neil McGuigan
Neil McGuigan

Reputation: 48267

This is not a great design.

You should read up on the Party Model, and Table Inheritance to see a design built to last. A skeleton:

PARTY
id

ORGANIZATION : PARTY
name
doing_business_as

INDIVIDUAL : PARTY
first_name
last_name

PARTY_RELATIONSHIP
from_party_id references party(id)
to_party_id references party(id)
from_date
to_date

ORGANIZATION_CONTACT_RELATIONSHIP : PARTY_RELATIONSHIP

Upvotes: 2

Neville Kuyt
Neville Kuyt

Reputation: 29649

I think there's a contradiction between your schema and the actual business domain. If some users are NOT associated with a business, there's a 0:n relationship, not a 1:n relationship.

In practice, I've seen several ways of modelling this. The simplest, of course, is not to create the foreign key relationship - and that's what the business domain seems to suggest (individual users are not associated with companies).

If there is a substantial difference between "business" and "individual" customers, you might factor the distinct columns into separate tables - so you might have "users", and "business_users" where the business info lives (inc. the link to companies_id), and "individual_users", where the individual data lives.

Upvotes: 0

Related Questions