July 22, 2011 at 9:14 am
Greetings again, super helpers.
I have a project that I am about to get into that requires proper db design.
I have started designing the tables but a bit stuck on the proper and accurate way to design it.
Here is the requirements and my attempt at designing the db.
Whatever help you can afford would be greatly appreciated.
Thanking you in advance.
****************************
We need a database designed for managing Dues/ Payments
Dues are $200.00 per year and is due Jan. 1 annually. If not paid within 30 days, interest accrues.
There is also a late fee of $35.00 that is charged. We would like database to auto calculate totals
including interest fees (18% per anum), late fee ($35.00), and violation fines ($25.00 a day).
We would like capture all names, addresses, phone contact info, email addresses if applicable.
We also would like it to have real time auto dates. Please use beginning date of January 1, 2007 and auto date yearly. Currently,
We have several collection reports summarized by each year. We want to use just one and have it to project updates year by year .
Members - Table
memberId pk identity seed
MemberFirstName nvarchar(50)
MemberLastName nvarchar(50)
MemberStrAddress nvarchar(150)
MemberCity nvarchar(50)
MemberState nvarchar(50)
MemberZipCode nvarchar(50)
MemberCellPhone nvarchar(50)
MemberOtherPhone nvarchar(50)
MemberEmailAddress nvarchar(50)
HOADues - Table
DuesId pk identity seed
Dues Money
CalendarYr - table
MonthName nvarchar(50)
Year int
Then my attempted DB Design. I really appreciate your help in helping me get all the tables needed for this project.
Again, thanks very much
July 22, 2011 at 12:29 pm
simflex-897410 (7/22/2011)
Greetings again, super helpers.I have a project that I am about to get into that requires proper db design.
I have started designing the tables but a bit stuck on the proper and accurate way to design it.
Here is the requirements and my attempt at designing the db.
Whatever help you can afford would be greatly appreciated.
Thanking you in advance.
****************************
We need a database designed for managing Dues/ Payments
Dues are $200.00 per year and is due Jan. 1 annually. If not paid within 30 days, interest accrues.
There is also a late fee of $35.00 that is charged. We would like database to auto calculate totals
including interest fees (18% per anum), late fee ($35.00), and violation fines ($25.00 a day).
We would like capture all names, addresses, phone contact info, email addresses if applicable.
We also would like it to have real time auto dates. Please use beginning date of January 1, 2007 and auto date yearly. Currently,
We have several collection reports summarized by each year. We want to use just one and have it to project updates year by year .
Members - Table
memberId pk identity seed
MemberFirstName nvarchar(50)
MemberLastName nvarchar(50)
MemberStrAddress nvarchar(150)
MemberCity nvarchar(50)
MemberState nvarchar(50)
MemberZipCode nvarchar(50)
MemberCellPhone nvarchar(50)
MemberOtherPhone nvarchar(50)
MemberEmailAddress nvarchar(50)
HOADues - Table
DuesId pk identity seed
Dues Money
CalendarYr - table
MonthName nvarchar(50)
Year int
This is a quick observation. Not perfect or all inclusive but a few things to think about.
I would go ahead and try to create the tables, attributes, constraints, and relations for the objects required based on your requirements and post them. You are going to have to create some program logic as well.
Your column definitions are way too big.
In the HOADues dues table you need to add the memberid column so that you can relate to the Member Table.
In the CalendarYr, the Month table is very wide. You may want to consider an identy on this one. Why did you select nvarchar(50)? Is this an international application? YOu should define the field to handle the maximin length of the data.
After you add the column in the CalendarYr and define it as a Primary Key then you want to create a the same column in the HOADues table.
You need to create Primary and Foreign Key Constraints, etc on all Tables.
HTH.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 22, 2011 at 12:46 pm
Wow, this sounds seriously like homework.
Before you could even start, you need to know what is expected with regards to calculating the interest on late dues payments. Is it compounded daily, instantly (required calculus), weekly, monthly, or something else?
You mention auto-calculating a $25/diem violation fine, but not whether that applies automatically every day, or has any other rules associated with it. Does interest on the late payment include the late fee and per diem fine as principle?
Too little data to even start designing this, beyond knowing you need a Members table, a Payments table, and a Calendar table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 23, 2011 at 8:15 am
Thanks guys for your responses.
I actually did ask for follow-up questions similar your comments GSquared.
I have not gotten a feedback yet.
LOL, this is not a homework.
You can look me up on this forum. I have been around for a long time.
It is just that I have not gotten the handle on effective db design.
I am more of a frontend guy.
Somehow, I just get this feeling that I either more tables or need to condense what I already have.
Then text I posted is all the requirements I was given.
Again, thanks a lot for your assistance.
July 23, 2011 at 8:33 am
If you collapsed the tables it would not be in 3rd normal form.
As was suggested you need to crete a payments Table.
In addition to adding the MemberID to the HOADues table you want to associate it to the CalendarYr Table.
You could create a month and a Year Lookup table as well. Is the year a calendar year or a fiscal year?
You could store the calculations in tables as opposed to hard coding it. That would make it easier to maintain.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 23, 2011 at 1:05 pm
Thank you Welsh.
Year is calendar year.
I am confused about something.
You said to create a Payments table.
How is different from Dues table?
Ok, I *think* I got it.
Dues stores the $200.00 annual dues and Payments when payment is actually made by a member?
As for calculating the dues, interests and late payments, I was thinking of adding a fieldname called TotalPayments to the Payments table and have that as calculated value.
In other words, calculate the interests, late payments, etc and dump into that field.
Isn't that a good idea?
Here is the latest based on you guys' recommendations.
Members - Table
memberId pk identity seed
MemberFirstName nvarchar(50)
MemberLastName nvarchar(50)
MemberStrAddress nvarchar(150)
MemberCity nvarchar(50)
MemberState nvarchar(50)
MemberZipCode nvarchar(50)
MemberCellPhone nvarchar(50)
MemberOtherPhone nvarchar(50)
MemberEmailAddress nvarchar(50)
HOADues - Table
DuesId pk identity seed
memberId fk foreign key (references Members table)
Dues Money
CalendarYr - table
memberId fk foreign key (references Members table)
MonthName nvarchar(50)
Year int
Payments - table
paymentid pk identity seed
paymentAmount Money
Thanks again.
July 24, 2011 at 1:00 pm
Dues and or fees are one entity and the payments are another a One to Many Relationship.
There should be a record entry for each payment not a column that is update the amount_due column.
You want to think through how you deal with the aggregate of payments and what TotalPaymwents means.
Don't forget to create a column that you can relate tables.
You could have multiple payments for dues.
You could also have monthly, quarterly and annual dues. If that is the case you would need to add a column.
I noticed that you do not have a due date?
The big question is how are you going to capture fees and where are you going to store it, etc.
If you think about it there is a lot to this assignment.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 24, 2011 at 2:16 pm
Please don't refer it to as an assignment because it gives the impresssion that is an elementary project.
It might be for you and the experts but as I stated, I am still trying to work through getting myself to understand db design
It is still a major learning curve for me.
Thanks again for your assistance.
July 24, 2011 at 2:44 pm
I meant that you were assigned a project.
It is not an elementary project and I did not mean to imply that.
You definetly was to get the requirements and design correct before you start writing code and capturing data.
Has anyone considered buy versus build?
You have some work to do. Get the requirements and think through it.
Good luck.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 24, 2011 at 9:47 pm
I really thank you and everyone else here for your great inputs.
You asked if they considered buying vs build.
No. The reason for that is we believe in our ability to write the code.
All we needed was a little help with the db design.
Quite frankly, I really didn't think it would be this difficult.
Of course that has more to do with my limited db design knowledge than anything else.
July 25, 2011 at 7:05 am
It looks like four tables needed, to me. Will probably require more, since you'll at least have some lookup tables and such, unless you confine those outside of scope.
I build a "Common" database on my servers, where I keep common lookup tables and functions, like a business calendar, a list of ISO-2 & 3 country codes and related data, a list of US states, a Numbers table, and so on. That way, I don't have to keep those in every database, just in one, and use synonyms to point to that database. If you have something like that, where common lookups are "out of scope", then you won't need those, but otherwise, you likely will.
You may also need to break up things like customers and their contact data (phone, e-mail, address), since it's pretty usual for databases to need those broken up for normalization, to allow things like mailing address, billing address, old addresses, et al.
Beyond those usual needs, four conceptual tables. The three I already mentioned, and one for Dues and Fees. You may or may not be able to include Interest as a line-item in the Dues and Fees table, depending on whether it is calculated at periodic intervals, or is instantaneous. If it is periodic, you can set up a job to calculate an insert rows there on the appropriate interval. If it's instantaneous, you'll probable be best off building a CLR UDF for that and run it when the statements are generated. Remember with either to find out what's expected in terms of rounding on it.
That should give you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 25, 2011 at 9:18 am
You said you were more of a front end person than a db person. Try thinking of your database as a place to store your front end information. You might think of the objects and events associated with what you are trying to accomplish.
Objects:
Members
Fees/Dues
Payments
Events/Methods:
MakePayment
AsssesFee/Dues
Think about the system as a whole and you can get a good idea of what you need for data. For example, how do you get the current balance for a member? Something like sum(dues) + sum(late fees) - sum(payments).
I agree with WelshCorgi that you need to look at your datatypes and make them more appropriate to the data. I doubt you will find that you need nvarchar(50) to store a maximum of 10 alphanumeric characters (12345-6789). You also don't really need to worry about extended characters in fields like state, phone numbers or email addresses.
When you think about your tables think about how they all relate. For example you have MemberID in the CalendarYr table. If you think about this it doesn't really work too well. The year does not belong to the Member and the Member does not belong to the year. The year would belong to the Dues. The way you have set up the CalendarYr table you have an entry for each month for each member. Then you would probably want something like a MembersDues table.
All thing being equal this rather looks like a book keeping program for your HOA? Unless you are wanting to expand your sql skills you may find this type of thing is almost easier to do using Excel than building a robust application to handle annual HOA dues collection. Either way you might consider making another distinction that instead of member you should use a Property table. People in the neighborhood will buy/sell their homes but the homes themselves are very unlikely to move around much.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 25, 2011 at 10:54 am
I agree with everything everyone said so far. I would just like to add that it may be easier for you to draw things out before you start physically designing the database. Grab a piece of scrap paper and draw out how each item relates to each person.
Here are a few references you can read up on to help your learning curve:
http://www.sqlteam.com/article/database-design-and-modeling-fundamentals
http://qa.sqlservercentral.com/articles/Database+Design/72054/[/url]
http://qa.sqlservercentral.com/articles/Database+Design/61489/[/url]
July 25, 2011 at 11:07 am
Yes, also grab some pencils. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 25, 2011 at 11:39 am
Welsh Corgi (7/25/2011)
Yes, also grab some pencils. 🙂
😛
@=)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply