Solved

Help with Sql Tables

Posted on 2014-01-01
6
384 Views
Last Modified: 2014-01-03
Hello Experts,

I am creating a database (taking help from experts-exchange)
Currently I have these tables,

1.People
PeopleID
Name
isSuperAdmin

2.Society
SocietyID
SocietyName

3.SocietyPeople
PeopleID
SocietyID
IsSocietyAdmin

What I need to track is, which SuperAdmin created SocietyAdmin member.
And which SuperAdmin/SocietyAdmin creates society people.
(SuperAdmin can create member for any society while SocietyAdmin can create members for his society only)

My previous quetion is posted here,
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28328699.html

Thanks in advance
0
Comment
Question by:johny_bravo1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39750636
I think you need this query:
Select SuA.PeopleID as SuperAdminID, SuA.Name as SuperAdminName,
SoA.PeopleID as SocietyAdminID, SoA.Name as SocietyAdminName
FROM People SuA INNER JOIN SocietyPeople s1 ON SuA.PeopleID = s1.PeopleID AND SuA.isSuperAdmin
INNER JOIN SocietyPeople s2 ON s1.SocietyID = s2.SocietyID
INNER JOIN People SoA ON SoA.PeopleID = s2.PeopleID AND s2.IsSocietyAdmin

Open in new window

0
 
LVL 12

Assisted Solution

by:Habib Pourfard
Habib Pourfard earned 250 total points
ID: 39750645
The tables you've designed aren't sufficient for what you need. you probably need another table to log who did what. like the one below:

Log
-----
ID
UserID
TableName
TableID
ActionType (Insert/Update)
Date
Notes
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 39750650
Hi chaau,
Thanks for the reply.
I need to add a "CreatedBy" column to track who add the members.

(
Which SuperAdmin created SocietyAdmin
Which SocietyAdmin created SocietyMember
Which SuperAdmin created SocietyAdmin
)

How the tables should be changed?
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 9

Expert Comment

by:sarabhai
ID: 39750681
If you want to track which one is create by whom
Then need to add extra column to each table that need to be tract

CreatedBy as int
LastUpdatedBy as int
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 39750986
Thank you experts.
What will the good approach :
1. Create a log table and put the entries for tracking who made the change
or
2. Put createdby in each table

e.g,

1.News
NewsiD
Title
Desc
ValidTill
SocietyId
CreatedBy (Better if we put it here and make relation with People?)

Or put the entry in log table?

There are other table also like Events/ Request and so on
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 39752606
SocietyPeople ( PeopleID, SocietyID, IsSocietyAdmin,
    CreatedPeopleID, --person who created this row, whether admin or not
    MadeSocietyAdminPeopleID ) --(last) person who set "IsSocietyAdmin" to true
0

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question