Page 1 of 1

Suggestions on table creations[SOLVED]

Posted: Thu Oct 20, 2005 7:34 am
by raghavan20
I am designing a new site and I want to have opinions on how you store usernames and member details.
Usually I used to have two tables UserNames_tbl and Members_tbl

Code: Select all

UserAccounts_tbl
=================
UserName
Password
MemberId
PrivelegeId

UserPriveleges_tbl
==================
PrivelegeId
PrivelegeDescription

Members_tbl
===========
MemberId
FirstName
LastName
Age
Gender
Email
Phone
Street
City
State
Country
PostCode
JoinDate
I am wondering whether you would club UserNames_tbl and Members_tbl together thus avoiding MemberId field or if you have a totally different approach...then I would like to hear from you.

Posted: Thu Oct 20, 2005 8:20 am
by feyd
If every user account makes a member record, there's no reason to have them separate that I can see.

Posted: Thu Oct 20, 2005 9:22 am
by raghavan20
do you mean to have it like this

Code: Select all

UserAccounts_tbl 
================= 
UserName 
Password 
PrivelegeId 
FirstName 
LastName 
Age 
Gender 
Email 
Phone 
Street 
City 
State 
Country 
PostCode 
JoinDate

Posted: Thu Oct 20, 2005 9:47 am
by Maugrim_The_Reaper
Aye...or yes.

If there's a strictly one to one mapping between table rows it makes more sense to build them into one table. There are situations where separation might be useful, but this not one.

Posted: Fri Oct 21, 2005 3:11 am
by raghavan20
Let's say we are going to make email address as the username and we are using this structure currently.

Code: Select all

UserAccounts_tbl 
================= 
UserName 
Password 
PrivelegeId 
FirstName 
LastName 
Age 
Gender 
Email 
Phone 
Street 
City 
State 
Country 
PostCode 
JoinDate
I am going to have another table called Orders which has to reference a member for every entry in it.
In such a case, if I put the username in the Orders table, its unnecessarily going to occupy so much space when the usernames are longer.
so in such a case, do you think having an MemberId in the same Members_tbl would be beneficial.
The proposed structure would be

Code: Select all

UserAccounts_tbl 
================= 
MemberId //newly added field
UserName 
Password 
PrivelegeId 
FirstName 
LastName 
Age 
Gender 
Email 
Phone 
Street 
City 
State 
Country 
PostCode 
JoinDate

Posted: Fri Oct 21, 2005 3:26 am
by n00b Saibot
Raghavan wrote:do you think having an MemberId in the same Members_tbl would be beneficial.
I always prefer ID's in every table since they can uniquely refer to every row in that table...