Suggestions on table creations[SOLVED]

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Suggestions on table creations[SOLVED]

Post 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.
Last edited by raghavan20 on Mon Nov 07, 2005 3:08 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

If every user account makes a member record, there's no reason to have them separate that I can see.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post 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...
Post Reply