How to map SQL database tables ? HELP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

How to map SQL database tables ? HELP

Author

Topic

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-05-17 : 15:38:19

I have a database in which there is a table with fields like:

--------------------

ID (Primary Key)

Name,

Address,

1990

1991

1992

1993
..
...
....
2010

2011

--------------------
or here is the skeleton
--------------------
CREATE TABLE [dbo].[MyCurrentTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[NAME] [nvarchar](35) NULL,
[ADDRESS] [nvarchar](20) NULL,
[1990] [money] NULL,
[1991] [money] NULL,
[1992] [money] NULL,
[1993] [money] NULL,
....
[2011] [money] NULL,
------------------------

Now I have to create a web project to maintain this database information. I am allowed to modify the database if required.

The years 1990, 1991 to 2011 represent the amount paid by the person. I want a functionality that if anyone is adding New record, by default the value should be current year and rest of the years becomes "History Record". Plus, I should be able to generate report of people who didn't pay in the current year.

Please help me how to map / modify / break / change this database to start this project ? I am a newbie, HELP !

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-05-17 : 15:45:32

Hi I was thinking about this, need your suggestions/advise:
-----------
Member_Table
-----------

CREATE TABLE [dbo].[MemberTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NULL,
[Address] [nvarchar](max) NULL, )

.....................................
-----------
Payment_Table
-----------

CREATE TABLE [dbo].[Payment_Table](
[PaymentID] [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NOT NULL,
[YearID] [int] NOT NULL,
[AmountPaid] [money] NULL,
[PaymentDate] [datetime] NULL,
[Status] [bit] NULL,)
..................................
-----------
Year Table
-----------

CREATE TABLE [dbo].[YearTable](
[YearID] [int] IDENTITY(1,1) NOT NULL,
[PaymentYear] [nvarchar](50) NULL,)

...........................................
ID from MembetTable wil be FK in Payment_Table
YearID from yearTable will be FK in Payment_Table
...........................................

In the PaymentYear field (year table) - I can manually enter values from 1990 to 2011, is it a good way of going ? Since admin of the database will be adding more coming years too like 2012, 2013 and so on...

Payment Status - I need this field to generate reports for people who made payments for specific year and who didn't. So that I can generate reports.

If I go with these 3 tables (mentioned above), will it be easy to retrieve/add/modify data and generate reports based on conditions ?

Is this a good way of normalizing tables ? if no, please advise.

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-17 : 16:36:42

looks ok I guess..there is no need to populate skeletal data though

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-05-17 : 16:42:55

thanks for the reply.
Could you please help me - how I can transfer data from my current table to these 3 different tables ? Please help !

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 07:12:19

If you still need help: What version of SQL Server are you using? If you are using SQL 2005 or higher, doing this becomes a little easier.

On the design itself: I like your design. If you have never designed a database before and this is your first, I would say you have a very bright future as a database developer!!

Couple of comments:

a) Many people would say that, while you can use any naming style that works for you, as long as you consistently use it, appending/prefixing the word "table" or "tbl" to a table name is an outdated style.

b) I have mixed feelings about the use of the YearTable. Having it is nice because querying is easier etc. On the other hand, the information contained in that table can perhaps be derived/calcualted, so intuitively, it would seem that you don't need that table. But, if I had to do it, I would do it the way you did.

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-05-18 : 07:21:30

Thank you for the reply and comments, I deeply appreciate it.
Just for information, if I don't create separate year table, how you will suggest me to do ? and will it be more easy that way or current way ? or doesn't matter ?

Yes, I need help with transferring data from my current single table (mentioned above in the 1st post) to these 3 new tables that I created. I am using SQL Server 2008 Management Studio Express.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 07:41:29

On second thoughts, life becomes much easier if you do it the way you did with an YearTable. I was thinking of simply not even using the yeartable and adding some kind of constraint to the PaymentTable.

For copying the data, I would do it in 3 steps. These are probably not the most elegant ways of doing it, but to my mind at least, the most simple and the most flexible.

Step 1: Populate the YearTable. Unless you have hundreds of years, just do it manually as in

insert into YearTable values (1990),(1991),(1992)...

Step 2: Populate the MemberTable. Here, I am assuming you want to keep the current customerId's from your current table.


SET IDENTITY_INSERT dbo.MemberTable ON;
INSERT INTO MemberTable
SELECT id, NAME, ADDRESS FROM MyCurrentTable;

BTW, it would be a good idea to break up the name into last_name, first_name, middle_name columns if possible. Similarly for address.

Step 3: Populate the Payment Table.

DECLARE @minYear INT, @maxYear INT;
SET @minYear = 1990; SET @maxYear = 2011;

DECLARE @yr INT;
SET @yr = @minYear;

WHILE @yr <= @maxYear
BEGIN
	DECLARE @stmt NVARCHAR(4000);
	set @stmt = 'INSERT INTO Payment_Table (YearID,AmountPaid) select ID, ['+CAST(@yr AS VARCHAR(4))
	+'] from MyCurrentTable';
	SELECT @stmt;
	--exec sp_executesql @stmt;
	SET @yr += 1;
END

In the above, I have commented out the sp_executesql statement, just so you can inspect what it is going to do before you actually do it.

I hope it goes without saying that I am just writing it and have not tested it.

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 08:47:04

dod you know how to script the DDL for your table?

Post that

Along with some sample data in DML form

see my link in my sig if you need help with that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-05-18 : 09:48:33

Hi sunitabeck,

Thank you so much for helping me with this.

Step 1: Successfully completed

Step 2: Yes, I do want to keep the ID same in MemberTable

I do have other fields in MyCurrentTable like ST, CIT (Which stands for state and city)
In my MemberTable I have created columns with State and City. (I have also renamed Name in MemberTable as FullName)
So how I will use the command you posted above so that value from MyCurrentTable - ST field should go into MemberTable - State field.
All I want to know is that how I will define in the above mentioned command that field of MyCurrentTable = New field in my MemberTable so that correct value should go in correct field in my MemberTable ?

Step 3: I am little confused with the above mentioned command. I just have to follow the same pattern for executing this command, right? (which is, right click on database > new query > enter command > execute)

By looking at the command I am getting confused how values will be saved from MyCurrentTable to PaymentTable based on the members and years in which they already made payments.

Would appreciate your help...

FYI:

Data in MyCurrent single table is like this (screenshot):

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 10:27:29

For step 2,
the syntax of the insert command is:


insert into dbo.MemberTable (col1, col2,.. colN)
select col1, col2, .. colN from MyCurrentTable

Because one of the columns in the destination table is an identity column, and you want to specify the values to be inserted into that column, you need the SET IDENTITY_INSERT dbo.MemberTable ON; So your statement would be something like this:


SET IDENTITY_INSERT dbo.MemberTable ON;
INSERT INTO MemberTable (id, lastname, firstname, SIT,CIT) -- specify in the correct order
SELECT id, lastname, firstname, SIT, CIT -- specify in the same order
 FROM MyCurrentTable;

For Part 3, if you run the code I posted earlier just as it is (with the commented out line kept that way --exec sp_executesql @stmt;), you will see that it is generating a set of insert statements. Examine the insert statements, change the query as needed to make sure that the insert statements are correct i.e., have the correct columns etc. Then, if you uncomment it and run again, those statements will get executed.

If you have difficulties, like Brett said, if you can post the complete DDL for your tables - that would make it easy to write the correct queries without guessing the column names.

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-05-18 : 11:00:42

Thanks sunitabeck,
Step 1: Successfully completed
Step 2: Successfully completed (DONE)

Now step 3: Yes, I am having a hard time in understand what max and minYear is doing in the above posted command.

What my understanding is that command should check the field/column name (since year are not saving as data in currentTable) with the Year_Of_Payment saved in YearTable (in relation to the memberID) - Plz correct me if I am wrong.

So here I am posting the DDL of my table so that I can get your help with the step 3 (inserting data from MyCurrent Table to PaymentTable)

(I have posted a screenshot of sample data above from MyCurrentTable)

------------
CREATE TABLE [dbo].[MyCurrentTable](
[ID] [int] NOT NULL,
[NAME] [nvarchar](35) NULL,
[LASTNAME] [nvarchar](20) NULL,
[ADDRESS] [nvarchar](20) NULL,
[PO BOX#] [nvarchar](20) NULL,
[CIT] [nvarchar](15) NULL,
[ST] [nvarchar](4) NULL,
[ZIP] [nvarchar](8) NULL,
[SUGSTDONTN] [money] NULL,
[1990] [money] NULL,
[1991] [money] NULL,
[1992] [money] NULL,
[1993] [money] NULL,
[1994] [money] NULL,
[1995] [money] NULL,
[1996] [money] NULL,
[1997] [money] NULL,
[1998] [money] NULL,
[1999] [money] NULL,
[2000] [money] NULL,
[2001] [money] NULL,
[2002] [money] NULL,
[2003] [money] NULL,
[2004] [money] NULL,
[2005] [money] NULL,
[2006] [money] NULL,
[2007] [money] NULL,
[2008] [money] NULL,
[2009] [money] NULL,
[2010] [money] NULL,
[2011] [money] NULL,
[2012] [money] NULL,)

------------------------
CREATE TABLE [dbo].[MemberTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](max) NULL,
[LastName] [nvarchar](max) NULL,
[Address] [nvarchar](max) NULL,
[City] [nvarchar](max) NULL,
[State] [nvarchar](max) NULL,
[Zip] [nvarchar](max) NULL,
[POBox] [nvarchar](max) NULL,
CONSTRAINT [PK_MemberTable] PRIMARY KEY CLUSTERED

----------------------
CREATE TABLE [dbo].[Payment_Table](
[PaymentID] [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NOT NULL,
[YearID] [int] NOT NULL,
[SuggestedDonation] [money] NULL,
[AmountPaid] [money] NULL,
[PaymentDate] [datetime] NULL,
[Status] [bit] NULL,
CONSTRAINT [PK_Payment_Table] PRIMARY KEY CLUSTERED

------------------

CREATE TABLE [dbo].[YearTable](
[YearID] [int] IDENTITY(1,1) NOT NULL,
[Year_Of_Payment] [nvarchar](50) NULL,
CONSTRAINT [PK_YearTable] PRIMARY KEY CLUSTERED

------------

I also realized that the field I created in my Payment_Table "PaymentDate" - The payment date is not available in the MyCurrentTable since it is only denoted by years. So I think for inserting value, we can leave this field NULL and in future this field will help in maintaining the data (in Payment_Table).

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 11:20:36

Since you have only a few years, it is probably simpler to do it manually one year at a time. So the code would be as follows. I am showing it only for 1990 and 1991. You will need to do this for each of the years. Also, I see a Status column in the Payment Table but do not see it in the MyCurrentTable.


--- 1990
INSERT INTO [dbo].[Payment_Table] (ID, YearId, [SuggestedDonation],[AmountPaid],[PaymentDate],[Status])
SELECT 
	m.ID,
	y.[YearID],
	m.[SuggestedDonation],
	m.[1990] AS AmountPaid,
	NULL AS PaymentDate,
	NULL AS Status
FROM 
	[dbo].[MyCurrentTable] m
	CROSS JOIN [dbo].[YearTable] y
WHERE
	y.[Year_Of_Payment] = 1990;

-- 1991
INSERT INTO [dbo].[Payment_Table] (ID, YearId, [SuggestedDonation],[AmountPaid],[PaymentDate],[Status])
SELECT 
	m.ID,
	y.[YearID],
	m.[SuggestedDonation],
	m.[1991] AS AmountPaid,
	NULL AS PaymentDate,
	NULL AS Status
FROM 
	[dbo].[MyCurrentTable] m
	CROSS JOIN [dbo].[YearTable] y
WHERE
	y.[Year_Of_Payment] = 1991;

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-05-18 : 11:49:41

Thanks sunitabeck,

Yes Status field is something I added extra in Payment_Table so that I will be able to generate reports of all the member who paid in specific year or not.

Status will work like "Paid" and "Unpaid" and in the database for "Paid" the value will be "True" and "Unpaid" value will be "False" - That why I defined Status as bit.

One question- In the above mentioned command, can as I save False value for Status so that for current year all the status will be unpaid ?

So it is going to be like this ?

INSERT INTO [dbo].[Payment_Table] (ID, YearId, [SuggestedDonation],[AmountPaid],[PaymentDate],[Status])
SELECT
m.ID,
y.[YearID],
m.[SuggestedDonation],
m.[1990] AS AmountPaid,
NULL AS PaymentDate,
FALSE AS Status
FROM
[dbo].[MyCurrentTable] m
CROSS JOIN [dbo].[YearTable] y
WHERE
y.[Year_Of_Payment] = 2011;

Once I transfer all the data successfully, I will be developing an ASP.C# project to generate reports / editing information / adding new records etc.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 11:55:47

You can do that, except, False should be in single quotes, as in

'False as Status

Alternatively, you can use 0 or 1 for False and True, respectively.

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-05-18 : 12:14:33

I was about to update my database and this question just came in my mind.

By the above mentioned command I will be able to transfer data but how to modify the command in such a way that I can update the status too ?

Something like, If amount is > 0 for specific year, status will be set as True for that particular year ?

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 12:36:10

You can change the 'FALSE' AS Status to


case when isnull(m.[1990],0) <> 0 then 'True' else 'False' end as Status[1990] <

You will need to change the column name from 1990 to the correct column name in each case.

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-05-18 : 12:48:12

Hi,

I am using this command for "1990" year

INSERT INTO [dbo].[Payment_Table] (ID, YearID, SuggestedDonation, AmountPaid, PaymentDate, Status)
SELECT
m.ID,
y.YearID,
m.SuggestedDonation,
m.[1990] AS AmountPaid,
NULL AS PaymentDate,
case when isnull(m.[1990],0) <> 0 then 'True' else 'False' end as Status[1990] <
FROM
[dbo].[MyCurrentTable] m
CROSS JOIN [dbo].[YearTable] y
WHERE
y.[Year_Of_Payment] = 1990;

---------------------------------
GETTING FOLLOWING ERROR MSG
---------------------------------
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '1990'.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 13:35:53

My bad. remove that [1990] < I think it was a copy and paste error, sorry about that.


case when isnull(m.[1990],0) <> 0 then 'True' else 'False' end as Status[1990] <

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-05-18 : 13:42:45

ok let me try and will post back the result

asp__developer
Posting Yak Master

108 Posts

Posted - 2011-05-18 : 13:48:43

Hi sunita,

I was getting some errors and I corrected the fields in the above mentioned command and guess what it worked !!! yeipiii

This is what I corrected:

INSERT INTO [dbo].[Payment_Table] (ID, YearID, [SuggestedDonation],[AmountPaid],[PaymentDate],[Status])
SELECT
m.ID,
y.[YearID],
m.[SUGSTDONTN],
m.[1990] AS AmountPaid,
NULL AS PaymentDate,
case when isnull(m.[1990],0) <> 0 then 'True' else 'False' end as Status
FROM
[dbo].[MyCurrentTable] m
CROSS JOIN [dbo].[YearTable] y
WHERE
y.[Year_Of_Payment] = 1990;

Thank you VERY MUCH SUNITA for helping me. I deeply appreciate it !!!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 14:02:32

I am glad, and you are quite welcome!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources