How to have same UserID for Member and Supplier

[theITvideos]

Hi there,

 

I must say this forum looks great and its good to be here.

 

Now I'll get straight to the point. I am working on a website where supplier and a member should be able to login with a single UserID.

 

That is, a member can be a supplier at the same time (logged in with the same ID) and if he is the supplier, he should be given a link taking him to the supplier admin area. If he is just a member, that supplier link will not be there.

 

Having a separate ID for member and supplier is not what is required. If the member is a supplier there will be a supplier admin link.

 

Looking forward to hearing from you guys!

 

Any inputs/feedback is always welcome.

 

Cheers!

 

Sid.

 

[JonnoTheDev]

Use a flag in your users database table to define a supplier i.e

 

members

====

memberId (INT)

name (VC 50)

username (VC 10)

password (PASSWORD)

supplier (ENUM 1,2)

 

Normal members will have a value of 1 in the supplier field. Suppliers will have a value of 2. As it is an ENUM field it can only contain one or the other value. Any members that are flagged with a 2 in this field you can display the link for. In the suppliers area of the website make sure you repeat the test that checks the value is equal to 2. If it isn't then redirect the user to a 404 page or another page on your website.

 

[theITvideos]

Hi Neil,

 

Thank for the reply. I will create table this way.

 

I am also going to maintain a supplier table where I will maintain details related to suppliers.

 

Can I create PK-FK relationship between members and suppliers tables?

 

Can we do:

 

Members Table

memberID (PK)

 

Supplier Table

supplierID (FK)

 

Ok and now the second part of the equation is... few suppliers can only be a supplier and not a member. How do we go about that.

 

Kindly reply Neil.

 

Thanks!

[JonnoTheDev]

You do not want 2 tables as you will be creating 1 to 1 relationships, this is pointless i.e 1 member can only relate to 1 supplier. Add all fields to the members table, if they dont apply to all members i.e only suppliers set them to have a default value of NULL.

 

If it is the case that there are 3 user types, i.e members, suppliers, & both, then again use a flag to distinguish the user type. i.e

 

members

====

memberId (INT)

name (VC 50)

username (VC 10)

password (PASSWORD)

memberType (ENUM 'general','supplier','both') DEFAULT general

supplierField1 (VC 20 NULL)

supplierField2 (VC 20 NULL)

 

In the above structure the type of member is defined by the memberType field, they are a 'general' user, a 'supplier', or 'both' types. The following fields only contain data when the user is part of the suppliers group. If they are general members then the fields are NULL.

[theITvideos]

Hi Neil,

 

Things are beginning to get clearer now. There is a reason why they call you guru 

 

My manager initially wanted to have a single table (combined supplier + member) then later said to make a separate table for the suppliers.

 

Now I will have to convince them that there are going to be 3 types of users and keeping a single table is good.

 

I hope they will get the picture.

 

Thanks Neil you are the man!

[JonnoTheDev]

There is a reason why they call you guru

I get called a lot worse sometimes

My manager initially wanted to have a single table (combined supplier + member) then later said to make a separate table for the suppliers.

There is nothing wrong with this however when sticking to database normalization practices, 1 to 1 relationships are rare and usually should be combined into a single entity (i.e 1 table). There are cases where a 1 to 1 relationship would be more efficient in terms of application speed i.e

 

If you had a website that contained 100,000 news articles, you would expect to have a table such as:

 

articles

=====

articleId

title

content

author

created

 

However, on most of the website pages I only display the title, author and the created date. I only display the text of the article when a user clicks on the title. Therefore as the 'content' field is a text field it takes up more memory when querying the database table, so as I do not display the article text very often, it makes my application much faster if I was to split as follows:

 

articles

======

articleId

title

author

created

 

article_text

=======

articleId

content

 

In your case you probably aren't going to be massively querying the members table so there is no real need to split data for a single member of a different type.