How to use a table when the user identifier can be one of two fields

Question

Recently we've started developing Github like organisation system for our service.

We have both free and payed plans.

There are users. Also there should be the owner for organisation and the owner has to register his credit card for the payed plan.

Owners can invite any users.

We cost each users and owners separately.

For example,

ManA registered with his email ManA@gmail.com ManA also opened up his organisation named "OrgA"with his email OrgA@gmail.com I'm struggling with how I can architect the table like Github.

I thought "Pivot table" would be great but it's okay as long as the email is the same. (ManA also opened up his organisation named "OrgA"with the same email as he uses for his personal account. ManA@gmail.com)

Payment info is also same as the email. ( Any users can register another billing info for his own organisations.)

I just wonder how I could implement the good entity relations.

NOTE: This is as the same as I posted on https://stackoverflow.com/questions/38096718/how-to-use-a-table-when-the-user-identifier-can-be-one-of-two-fields.

But maybe here is the right place to post, so as soon as I get some answers, I will delete posts in stackoverflow.

##### Update ######

Scenario

I am new to databsae design, and I decided to develop to make Github like organisation billing system. However I am having trouble modeling and normalizing it.

The databse is meant to store data about Credit card info (Actually it will be token though.), Users, Organization, Billing

Business rules

• A User can have multiple billing information
• A User can join any organizations
• A User can be an organizer.
• A User has to pay for his own usage.
• A User can separate his creditcard billing information
• An Organization always has an organizer
• An Organizer has to pay for usages made by users in the group
• An Organization has a name and an emai.

The issue

I'm struggling to apply any relations between tables efficiently. Also the problem is that if the credit_card has changed the billing history will be gone with this implementation. So I'd love to put Billing relations to Person & Organization, however it will be mess. Any ideas?

Model

##### Update 2016 Jul 8th######

##### Update 2016 Jul 8th (2)######

Answer 1

When doing this sort of design, I think first about the "things", and then about the "relationships". The things are:

• the people
• the credit cards and
• the organisation.

The relationships are:

• Who owns the organisation.
• Who is a member of the organisation.
• Which credit card is the owner using to pay the bill

You need an entity for each "thing". Depending on whether it is a "to one" relationship or a "to many" relationship, you can either define the relationship within an entity or you will need a new entity for the relatonship.

Who owns the organisation: If only one owner, you can put the owner in the organsation entity, otherwise you would need a separate entity. Who is a member of the organisation: You can have many members of an organisation, therefore you will need a separate entity. Which credit card is the owner using to pay the bill: Only one credit card would be used therefore you can put the reference to the credit card card in the organisation entity.

So you would have four entities: Person (a "Thing") Credit Card (a "Thing") Organisation (a "Thing", with two relationships) Organisation_Member (a relationship)

Here are some example columns you could use for the entities

Person Columns:

• person_id (primary key, identity)
• name
• email

Credit Card Columns

• person_id
• billing_id (start from 1, don't make it an identity)
• +credit card information columns (I expect you would encrypt the data, so not store the actual card numbers as text)

(The combination of person_id and billing_id in the above forms the primary key)

Organisation Columns:

• org_id
• org name
• person_id (of the (one) owner - defining one relationship mentioned)
• billing_id (of the (responsible) owner - defining another relationship mentioned)

Organisation_Member:

• org_id
• person_id (of the member)

One more point about email addresses. If you want there to be a separate email address for the organisation, it can be included in the organisation table. I'm not sure I understand why you would need this. Perhaps my answer would be different if I understood what it is for.