We help IT Professionals succeed at work.

Sql tables creation

312 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?
Comment
Watch Question

Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
"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, ... )

Author

Commented:
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.
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
CERTIFIED EXPERT
Top Expert 2011

Commented:
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....

Author

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.