I agree with Stephen on this as well. If you want to constrain the data
and you use stored procs for entry, use datepart functions to verify the
data in that column.

-Steve Hughes
-----Original Message-----
From: M Stephen Kalevik
[mailto:mssqldba-ezmlmshield-x66606253.[Email address protected]
Sent: Wednesday, June 28, 2006 1:14 PM
To: LazyDBA Discussion
Subject: RE: Opinions (and I know you have 'em)

You wouldn't need to join with anything. You would just query based on
that criteria and column within the transactional table.
Having that column defined to only allow valid years for insert. Read my

previous comment.

Making a separate table with only one column is not necessary. Using
this
philosophy would be like creating a lookup table for every column in
every
table.
When I post a database design schema I don't show the lookup tables.

When the domain is year and it is already represented by internal
datatypes ( datetime and smalldatetime ) I can format for display or use

datepart functions to
pull out the piece I want. Or I can create my own user defined datatype
and bind it to a rule which indicates the valid values.






"Thinus van Rooyen "
<mssqldba-ezmlmshield-x95246203.[Email address protected]
06/28/2006 09:23 AM

To
"LazyDBA Discussion" <[Email address protected]
cc

Subject
RE: Opinions (and I know you have 'em)






Hi all,

I would have done exactly the same as Brendt's 'dba' - probably due to
lack of experience...

All your comments makes sense, but as noted by Mordechai, how would you
represent the data? Would you have a table with one column(year)

How would you join this table with say a transactional table if you need

to see all tranactions for a specific year?

Thanks
Thinus

-----Original Message-----
From: M Stephen Kalevik
[mailto:mssqldba-ezmlmshield-x68069258.[Email address protected]
Sent: Wednesday, June 28, 2006 5:12 PM
To: LazyDBA Discussion
Subject: Re: Opinions (and I know you have 'em)


I agree. The year is unique without having an additional ID. A user
datatype for how you want represent year might be useful with a rule
bound

to it for valid years might be useful. Or a validation table that has a
list of valid years.





"Brendt Hess " <mssqldba-ezmlmshield-x39229436.[Email address protected]

06/27/2006 06:36 PM

To
"LazyDBA Discussion" <[Email address protected]
cc

Subject
Opinions (and I know you have 'em)






I am in the process of evaluating and revising an existing DB that was
designed by the previous 'dba' (a programmer with minimal understanding
of
relational design), and I came across this table:

Year

It contains two columns:
year_id decimal(18,0)
year int

Now, our parts data needs to reference the year (motorcycles, you know),
BUT.....

So, I decided to ask. Is there *anyone* that believes this is a correct
normalization step?

Note that a part is specifically related to one or more make/model/year
combinations, so it is possible that someone might think that this would

be
correct. But really, how can replacing one constant value with a larger
constant value, both of which will never change, be considered valid
normalization? It's like the people who replace state codes (char(2)
fields) with a table of states and an int ID field (4 bytes), when the
state
codes themselves could be the identifier.

Opinions? Yeah, I have 'em. I just want to hear some others, so that I

can
see if I am off on a tangent here.

Brendt




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




MS Sql Server LazyDBA home page