Solved

Sql tables creation

Posted on 2014-01-02
7
246 Views
Last Modified: 2014-02-24
Hello Experts,
I am creating below table , need your help with this

1.IncomeMaster
IncomeId
IncomeName

2.SocietyIncome
SocietyId
IncomeId

3.SocietyIncomeDetails
SocietyIncomeId
IncomeId
SocietyId
Details
MemberId(Not sure if this should be here)
CreationDate

Scenario is that, there are multiple societies. Society 'A' may have some different income sources, society 'B' has some different income.
So I created SocietyIncome table. We need to give admin option to set Income sources for his society.

Now in some Income source, that Income is received by Member or as overall income.
e.g, Maintenance Fee is a memberwise income, need to track which member has paid maintenance
while Renting Society ground for Events is a income by society.

So how should be table formed?
0
Comment
Question by:johny_bravo1
7 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 39751152
Hi.

Maybe consider something more like this:

SocietyMaster: master table with your society details
SocietyId
Details
CreationDate

IncomeMaster: master table with your income details
IncomeId
IncomeName
MemberId - tags member-specific income; NULL otherwise

SocietyIncome: associative table linking income to society
SocietyId
IncomeId

For the income table, if income can occur at different points, you may need to have some aspect of date/time.

I hope that helps.
0
 
LVL 18

Assisted Solution

by:Jerry Miller
Jerry Miller earned 250 total points
ID: 39751356
In addition to what Kevin Cross stated above, I would also have two additional tables. I generally put in some auditing type fields too. This way I can track who added / Updated the record.

One for Member information:
MemberID
MemberName
MemberAddress
etc.......
CreatedByDate
CreatedByID
ModifiedByDate
ModifiedByID
Active

And a second for Income types:
IncomeTypeID
IncomeName
CreatedByDate
CreatedByID
ModifiedByDate
ModifiedByID
Active

And update IncomeMaster more like this:
IncomeId
IncomeTypeID
MemberId
CreatedByDate
CreatedByID
ModifiedByDate
ModifiedByID
Active
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39751473
"Master" is not put in table names -- a table name w/o a suffix is considered a "master" if/when that's appropriate.

It helps to use an underscore (_) between the master tables in a relationship table.

Sorry, I don't have much time now, but here's a start:


Income ( IncomeId, Name, ... )

Society ( SocietyId, Name, Type?, ... )

Member ( MemberId, Name, ... )

-- identifies income sources and expected/scheduled payments, NOT payment details
Society_IncomeSource ( SourceId, SocietyId, IncomeId, CreateDate, PayDate, TotalAmount, ... )

-- identifies actual payment details ONLY, NOT income source nor schedule of payments
Society_IncomeDetails ( SourceId, ReceivedDate, MemberId (may be NULL if N/A), Amount, ... )
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 8

Author Comment

by:johny_bravo1
ID: 39754190
Hi ,
Thanks for the comments. But still I am confused.
Please take a look at my current tables
1.Member
MemberId
MemberName
FlatNo
IsSuperAdmin

2.Society
SocietyId
SocietyName
Address

3.SocietyMembers
SocietyId
MemberId
IsSocietyId

4.IncomeMaster
IncomeId
IncomeName

5.SocietyIncome
SocietyId
IncomeId

6.SocietyIncomeDetails
SocietyIncomeId
IncomeId
SocietyId
Details
MemberId
CreationDate


What I need is, SocietyAdmin for a particular society can set the Income Types for his society.
In SocietyIncomeDetails, the income may be gained by Member and sometimes it is gained as a society  income.In case the income is generated by renting ground, such income will have NULL in SocietyIncomeDetails for that row,
I am going to use Dynamic Data Entities, so just worried if this works fine with this.
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39754334
Where are you determining if the user is a SocietyAdmin? Your query will need to join tables based on SocietyID where SocietyAdmin's SocietyID = to the SocietyIncome.SocietyID
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39754361
could you take a step back and explain what this is all about ....

1. what do you mean by a society?  (e.g. what is the significance of flat number on the member table?)

2. are you basically assuming this is all about "rental" income , from rooms or equipment?

3. will membership dues  be an income source?

4. can non members be an income source (i think yes because of the null you described)

5. How do you want to deal with recurring income streams (ie hiring rooms for a year...)

6. how will you allow for multiple bookings/payments to be made by a member for several items   (e.g. a room , plus projector, lecturn,  tables/chairs.... and cleaning?)

7. how will the system handle prepayment, re-imburments, discounts for multiple bookings, late-payments,....

8. i get the feeling that there should be some form of associated booking system, and resource / equipment inventory within the system maybe you think thats covered by the incomeid table?  but if so wont you need to have facilities for depreciation and age of the underlying resource.

9. breakages / unavailablity of resource through other causes (e.g. key misplaced)

please help us to understand your actual requirements....
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 39755793
Hi Lowfatspread,
"Society" is refered to Residential Apartment.
In Residential apartment the income sources are different.
Some income sources like maintenance fees are on monthly/ yearly basis.
So I need to track such income which are made from members.

Other income like if society has a theatre or play ground which is given on rent, then this income is society's income as it is society's property.

So some recurring incomes from member of the society and some income for renting society resources are gathered.

Sometime the income may be received by society if someone sells his falt , then he need to pay some amount to society.

I need to track these income and from which resource this income is generated.
Need to track income amount, date, pending fees( in case of recurring income like monthly fees)

If this information lacks anything please let me know.
Thanks for your time.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 43
What's the difference between these two "qualifiers?" 3 35
Troubleshooting Methodology - steps 3 22
Sql query 34 22
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now