Children become parents - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Children become parents

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-10-19 : 11:08:02

Hi,

I have two tables which both contain a column for each month.

Table1
======
pk int
Jan int
Feb int
...

Table2
======
pk int
Jan int
Feb int
...

Both tables have other columns which distinguish the two tables but I haven't shown them.

My idea was to factor out the months and put them in a seperate table.

So the two tables become 3.

Table1
======
pk int
monthsID int

Table2
======
pk int
monthsID int

Months
======
monthsID int (pk)
Jan int
Feb int
...

So the two tables each have a column which references a month in the months table.

I also added a relationship contraint and non null constraint such that Table1 and Table2 have to be referencing an existing row in the Months table.

Is this is a good design? I think it makes things much easier to code. However, I have a couple of doubts.

1. If a row is deleted from Table1, the referenced Months row will continue to exist even though it seems wrong for this to be possible. It seems to me that the row in the Months table is a child of the row from Table1 yet the defined relationship is the other way around.

2. A single row in the Months table can be referenced more than once. This situation could not occur if the actual months data was within Table1 and Table2.

What would be cool is if you could define a one-one constraint.

Any thoughts?

X-Factor

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-10-19 : 11:37:16

You could use a trigger to ensure both conditions.

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-19 : 11:55:46

Want to take the next logical step...what about years?

What does having only 12 "containers" buy you?

Will you comingle data over years? If so, how will you know what's what?

So let's say you purge the data so you always just have data for 1 year. Just seed the table with your 12 months and RESTRICT DELETE..no need for a trigger.

Brett

8-)

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-10-19 : 12:07:50

Thanks for your replies.

Sorry X002548 but that's a little brief for me. Please could you explain further?

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-19 : 12:24:27

Well I really don't see the benefit of creating the month table unless everything is set in English
and you want to retrieve the date name in another language (ex. Spanish).

and about the relation ships you should have 12 rows on the month table with a relation ship of one
to many from the month table to the other two and you won't be able to delete a row from the table
month if there are any records in any of the other two tables but yes the opposite

I also don't think it will be a better design than just having the two original tables
For example if you use inner join for queries using the two original tables now you will have to use
three instead you can use

DATENAME ( datepart , date )

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-19 : 12:57:36

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myMonths99(myMonth varchar(3), MonthOrder int, Bucket money)
GO

-- Seed the Table

INSERT INTO myMonths99 (myMonth, MonthOrder, Bucket)
SELECT 'JAN',1, 0 UNION ALL
SELECT 'FEB',2, 0 UNION ALL
SELECT 'MAR',3, 0 UNION ALL
SELECT 'APR',4, 0 UNION ALL
SELECT 'MAY',5, 0 UNION ALL
SELECT 'JUN',6, 0 UNION ALL
SELECT 'JUL',7, 0 UNION ALL
SELECT 'AUG',8, 0 UNION ALL
SELECT 'SEP',9, 0 UNION ALL
SELECT 'OCT',10,0 UNION ALL
SELECT 'NOV',11,0 UNION ALL
SELECT 'DEC',12,0
GO

DENY INSERT, DELETE ON myMonths99 TO Public
GO

SET NOCOUNT OFF
DROP TABLE myMonths99
GO

[/code]

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-19 : 13:25:49

you have the right idea, though you might not even need the Months table. nothing wrong with:

ID, MonthNumber

being a composite PK for a table, even if MonthNumber doesn't relate to another table. There's no rule saying that all PK columns must reference a foreign table. A simple constraint of "MonthNumber BETWEEN 1 and 12" gives you all the data integrity you might need, w/o using a FK constraint.

If you have lots of reports, though, they require 1 row per month and sometimes you might have data missing in your tables, then a Months table will allow you to LEFT OUTER JOIN from all months to your data if needed.

Either way -- you are taking the right approach to normalize your tables. Having a table with columns like ID,Jan,Feb,Mar...Dec is almost never a good idea.

Don't forget, also, that if you need to you can always cross-tab your data back into a months-as-columns format as needed; search this site for posts and articles on Cross-Tabs.

- Jeff

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-10-20 : 04:08:29

I'm not sure if I have explained this clearly.

Each and every row in Table1 and Table2 references a row from Months.

Each row in the Months table has a value set against every month in the year. The columns are the months - not the rows.

So, X002548, I don't think the table that your query creates will work for this situation because it only stores one value in total per month when, in fact, I need one value for each month per row from Table 1 and Table 2.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-20 : 04:57:58

quote:

1. If a row is deleted from Table1, the referenced Months row will continue to exist even though it seems wrong for this to be possible. It seems to me that the row in the Months table is a child of the row from Table1 yet the defined relationship is the other way around.

yes, but you still have table2 referencing to it, actually the months is the parent, you will not be able to add data in table1 if the month does not exist in months table

quote:

2. A single row in the Months table can be referenced more than once. This situation could not occur if the actual months data was within Table1 and Table2.

yes and you'll be able to check if there are entries for a specific month. months table becomes the control over the two tables especially if table1 and table2 are somewhat related in your business logic.

--------------------
keeping it simple...

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-10-20 : 06:21:29

quote:
yes, but you still have table2 referencing to it

No!

I hope my later explanation of the situation is clear.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-10-20 : 06:40:43

Here is what is some SQL.

USE tempdb
GO

CREATE TABLE Months
(
	YearID int NOT NULL,
	Month1 int,
	Month2 int,
	Month3 int,
	Month4 int,
	Month5 int,
	Month6 int,
	Month7 int,
	Month8 int,
	Month9 int,
	Month10 int,
	Month11 int,
	Month12 int
)

CREATE TABLE Table1
(
	pk int,
	YearID int NOT NULL,
	DistinguishingAttribute1 int
)

CREATE TABLE Table2
(
	pk int,
	YearID int NOT NULL,
	DistinguishingAttribute2 int
)

ALTER TABLE Months WITH NOCHECK ADD CONSTRAINT [PK_1] PRIMARY KEY  CLUSTERED (YearID) ON [PRIMARY] 
ALTER TABLE Table1 ADD CONSTRAINT [FK_1] FOREIGN KEY (YearID) REFERENCES Months (YearID)
ALTER TABLE Table2 ADD CONSTRAINT [FK_2] FOREIGN KEY (YearID) REFERENCES Months (YearID)


INSERT INTO Months
(
YearID, Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12 
)
VALUES
(
1, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
)

INSERT INTO Table1
(
pk, YearID, DistinguishingAttribute1
)
VALUES
(
1, 1, 23
)

INSERT INTO Months
(
YearID, Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12 
)
VALUES
(
2, 12, 21, 34, 43, 54, 65, 76, 82, 91, 103, 115, 121
)

INSERT INTO Table2
(
pk, YearID, DistinguishingAttribute2
)
VALUES
(
1, 2, 999
)

SELECT t.pk, t.DistinguishingAttribute1, m.* FROM Table1 t INNER JOIN Months m ON t.YearID = m.YearID
SELECT t.pk, t.DistinguishingAttribute2, m.* FROM Table2 t INNER JOIN Months m ON t.YearID = m.YearID

DROP TABLE Table1
DROP TABLE Table2
DROP TABLE Months
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-20 : 08:57:37

NO -- that is NOT a good design. Do not store data like that in COLUMNS -- it goes against relational database design. When you store repeating data, store it in multiple ROWS not COLUMNS.

You table of MOnths should have 1 month per row. if you have other tables with other monthly data, do NOT store it like this:

ID, Month1, Month2, Month3 ... Month12

Because what does those "Month" values mean? Amounts? Dates? Units? You cannot properly label the data in those months, and you can only store ONE value per month per row. Also, you cannot easily query such as "give me totals from June-Aug" using simple parameters.

If you store the data like this:

ID, Month#, Value1, Value2, .... Value3

Now your data is normalized and you can store as many values per month as you like, all clearly labelled. and you can very easily say "give me data from month @StartMOnth to @EndMonth" via parameters.

Read a book on normalization and database design, maybe someone can chime in with some good links for you. Think carefully and logically about the implications of the particular design you have described and the needless complexity, and then consider what I have explained to you and you will see it logically makes good sense.

- Jeff

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-10-20 : 10:18:34

Thanks for your help. I'm afraid this isn't making much sense.

NO -- that is NOT a good design. Do not store data like that in COLUMNS -- it goes against relational database design. When you store repeating data, store it in multiple ROWS not COLUMNS.

This isn't repeating data. Its a fixed set of values and I only want one value per month per row. The Months table just holds a row of data which covers each of the months in the year.

How about if I called the table 'YearlyCostsByMonth'?

I think my design would fail first normal form if the number of months in the year was variable. But it isn't.

the particular design you have described and the needless complexity

So if I used the design that you are suggesting, how would I achieve the results from the SELECT query I listed? I bet the query would be alot more complicated than a simple SELECT with a join. I think it would require a pivot table. And how would I make sure that each record in Table1 and Table2 was associated with a complete set of yearly data?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-20 : 10:21:24

yes. by definition pivot-tables are a presentation issue and should not affect how you store the data.

re-read the other points I made, about storing 1 value per month only and not being able to "label" each value. I can't really debate or completely explain relational database design in this thread, go with what you feel is right, but at least think about the implications of updates, inserts, deletes, and other queries other than the cross-tab one to retrieve data from your table.

- Jeff

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-10-20 : 10:39:16

ID, Month#, Value1, Value2, .... Value3

..looks a little like..

ID, Month1, Month2, Month3 ... Month12

..anyhow.

If I had one row per month, wouldn't it be better to to associate the month with one value and perhaps a type or version identifier?

I acutally think that using the design I've shown makes doing INSERTS and UPDATES much much easier because I've the way the data is being used. So for now I think I might just leave it in a de-normalised state.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-20 : 10:40:28

by the way -- a 12 month pivot table is pretty easy, and you don't have to worry about storing data for all months. that's the whole point -- you store data correctly, and then worry about how to present it later. Now and then you have to make concessions for presentation purposes in your DB designs, but always as a last resort.

if you physically store your table this way:

ID, Year, Month, Amount (PK of ID,Year, Month)

with data like this:

ABC, 2003, 1,20
ABC, 2003, 3,40
ABC, 2003, 12,10
DEF, 2004,1,10
DEF, 2004,6,20
DEF, 2004,8,30
DEF, 2003,12,90

then you can manipulate and query your data very easily. and, to see things in a cross-tab format, you just create a VIEW and refer to it whenever you need it. you have the best of both worlds -- normalized data and easy presentation:


create view CrossTabeView
AS
select ID, Year,
  SUM(CASE WHEN Month=1 THEN Value ELSE 0 END) as Month1,
  SUM(CASE WHEN Month=2 THEN Value ELSE 0 END) as Month2,
  SUM(CASE WHEN Month=3 THEN Value ELSE 0 END) as Month3,
  ...
  SUM(CASE WHEN Month=12 THEN Value ELSE 0 END) as Month12
from
  YourTable
group by ID,Year

simple as that -- take 2 minutes to write the view, but now you are done.

again, do some more research on normalizationa and how to structure a relational database. But never confuse displaying data with storing data -- they are two distinct issues that should be handled almost independantly of each other.

If your data is stored properly, no matter how you'd like to display it, it can be done. but if you store your data based on a particular output you are requiring, it makes no sense because what about OTHER presentations you made need to write for that data? Those will suffer for the sake of the first one.

- Jeff

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-10-20 : 10:41:54

OK Jeff. Thanks for your help.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-20 : 10:42:26

no -- when I said value1, value2 I meant those "values" represented things like:

ID, Month, Amount, Units, Comments, Tax, SalesPersonID, ... etc ....

I should not have listed it as Value1, Value2 -- it was confusing I admit. Does this make sense? Again, this is database 101 and really a key concept to grasp before moving on with your design, so spend time researching and really make sure you get all this ....

- Jeff

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-10-20 : 10:56:15

Yes I think I do get it.

Another question though...

I'm loading data from these tables into a .net DataSet and the DataSet is used to populate form elements.

The form elements, i.e. the presentation, are fixed such that they require the data presented in the way that I have been storing it.

Do you think its valid to de-normalize the data and store it in the DataSet in the way that I have been incorrectly storing it in the database due to the fact that the DataSet only exists to populate this particular form? So, in effect, the DataSet hold a de-normalised view of the properly normalsed database.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-20 : 11:14:55

quote:
Originally posted by X-Factor

Do you think its valid to de-normalize the data and store it in the DataSet in the way that
I have been incorrectly storing it in the database due to the fact that the DataSet only exists
to populate this particular form? So, in effect, the DataSet hold a de-normalised view of the
properly normalsed database.

instead of that create a View as Jeff explained and change the source of the DataSet to the view
you create

quote:

Originally posted by jsmith8858

create view CrossTableView
AS
select ID, Year,
  SUM(CASE WHEN Month=1 THEN Value ELSE 0 END) as Month1,
  SUM(CASE WHEN Month=2 THEN Value ELSE 0 END) as Month2,
  SUM(CASE WHEN Month=3 THEN Value ELSE 0 END) as Month3,
  ...
  SUM(CASE WHEN Month=12 THEN Value ELSE 0 END) as Month12
from
  YourTable
group by ID,Year

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-10-20 : 11:21:53

Hmmm, I got an error when I tried dragging a view onto the typed dataset designer.

Though I can build a DataTable which maps to the view or maybe its possible to use a DataView to do the pivot.

But yeah, that's what I had in mind.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources