I'm building a database that has maybe four unique tables Student,
Advertiser, Employee, maybe Account. Three of the four table (Student,
Advertiser, Employee) have something in common in which they all contain
fields such as emailAddress, password, role, isAccountActive, etc. which
allow them to access their respected data. However, is it best practice to
build a fourth table which contain Account information or should I just
include that information in their respected tables?
My thinking is that if you have a fourth table such as Account then you can
manage all accounts (Student, Advertiser, Employee) from one table, but as
the database gets more in-depth you have to build more and more complex
stored procedure to do simply task such as update, delete, select, etc.i would probably combine all 4 tables into one because of the columns the first three have in common
it really depends on how many columns they don't have in common|||try reading this.
Database Design for Mere Mortals (http://www.amazon.com/gp/product/customer-reviews/0201752840/102-7659258-1375352?_encoding=UTF8&me=ATVPDKIKX0DER&s=books)|||in addition, i truly beleive that the data model is entirely indicative of the business model or in other words, the data model already exists it's your job to discover it.
the rules are defined as a dictum for all to follow. some follow them more strictly than others and some bend the rules to accomodate performance and simplicity requirements.
as a designer, you should know the rules before you can intelligently break them.|||the rules are defined as a dictum for all to follow. some follow them more strictly than others and some bend the rules to accomodate performance and simplicity requirements.you'll love this discussion, then --
http://www.kottke.org/04/10/normalized-data
by the way, i trust that remark about making an attempt to read a web page on amazon was not directed at me :)|||you'll love this discussion, then --
http://www.kottke.org/04/10/normalized-data
by the way, i trust that remark about making an attempt to read a web page on amazon was not directed at me :)
what the hell are you talking about?
by the way i read your link and that is just another example of a application developer assuming that he has the chops for db work. just because you can type create table create view and creat proc , in no way makes you a dba it just makes us all look bad when the shiat hits the fan and said app developer cant fix it.
I hate this subject so much that just a 5 minute exposure to this article has totaly pissed me off.
"ooooooohhh i hate that rabbit"|||what the hell are you talking about?i was talking about your comment in post #3 which immediately followed my post #2 -- i assume your rather arrogant comment "try reading this" wasn't directed at me
you should read that kottke article a little more closely, it is the comments to the article that are all the fun, kottke himself is not a dba, he was just raising the issue
if the name kottke means nothing to you, that's fine, i guess you haven't been around the web much
the name cal henderson may not mean anything either, but he is the one who developed the back end for flickr, and you really should read his powerpoint presentation, flickr is an amazing project and i personally don't have th chops to do anything remotely like that
maybe you do, though
:)|||R2D2.
against my better judgement, I will issue the following statement.
my initial post was directed to the guy who started this thread.
my follow up post was a kind of postscript to the previous post and once again was directed to the guy who started this thread
at no point were you even on my radar.|||I just want to thank you guys for the help. I have now made a sound decision on my database design.|||...and off to the holy quest, to seek the holy model...
...and sometimes our heated discussions remind me of this opening scene (http://www.mwscomp.com/movies/grail/grail-01.htm)...|||at no point were you even on my radar.oh, that's so adult of you, rupert|||Neutral corners, please!
Showing posts with label unique. Show all posts
Showing posts with label unique. Show all posts
Saturday, February 25, 2012
Friday, February 24, 2012
best approach "wher in" or JOIN
Hi,
I have 2 sets
Set1 has few UNIQUE items say workitem Ids 2,3,4
Set2 has multiple items say workitems Ids is 1,2,2,4,3,5,4,3,6,7,8
I need to look for Set1 in Set2 so i get 2,3,4 etc...
which is the best way
Set2 Left join Set1
OR
Select item from Set2 where item in (Set1)INNER join, not LEFT OUTER|||I posted your answer over at SQLTeam|||see if this helps
books online {Using EXISTS and NOT EXISTS to Find Intersection and Difference} QUOTED IN ENTIRETY
Subqueries introduced with EXISTS and NOT EXISTS can be used for two set-theory operations: intersection and difference. The intersection of two sets contains all elements that belong to both of the original sets. The difference contains elements that belong only to the first of the two sets.
The intersection of authors and publishers over the city column is the set of cities in which both an author and a publisher are located.
USE pubs
SELECT DISTINCT city
FROM authors
WHERE EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)
Here is the result set:
city
---
Berkeley
(1 row(s) affected)
Of course, this query could be written as a simple join.
USE pubs
SELECT DISTINCT authors.city
FROM authors INNER JOIN publishers
ON authors.city = publishers.city
The difference between authors and publishers over the city column is the set of cities where an author lives but no publisher is located, that is, all the cities except Berkeley.
USE pubs
SELECT DISTINCT city
FROM authors
WHERE NOT EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)
This query could also be written as:
USE pubs
SELECT DISTINCT city
FROM authors
WHERE city NOT IN
(SELECT city
FROM publishers)
I have 2 sets
Set1 has few UNIQUE items say workitem Ids 2,3,4
Set2 has multiple items say workitems Ids is 1,2,2,4,3,5,4,3,6,7,8
I need to look for Set1 in Set2 so i get 2,3,4 etc...
which is the best way
Set2 Left join Set1
OR
Select item from Set2 where item in (Set1)INNER join, not LEFT OUTER|||I posted your answer over at SQLTeam|||see if this helps
books online {Using EXISTS and NOT EXISTS to Find Intersection and Difference} QUOTED IN ENTIRETY
Subqueries introduced with EXISTS and NOT EXISTS can be used for two set-theory operations: intersection and difference. The intersection of two sets contains all elements that belong to both of the original sets. The difference contains elements that belong only to the first of the two sets.
The intersection of authors and publishers over the city column is the set of cities in which both an author and a publisher are located.
USE pubs
SELECT DISTINCT city
FROM authors
WHERE EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)
Here is the result set:
city
---
Berkeley
(1 row(s) affected)
Of course, this query could be written as a simple join.
USE pubs
SELECT DISTINCT authors.city
FROM authors INNER JOIN publishers
ON authors.city = publishers.city
The difference between authors and publishers over the city column is the set of cities where an author lives but no publisher is located, that is, all the cities except Berkeley.
USE pubs
SELECT DISTINCT city
FROM authors
WHERE NOT EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)
This query could also be written as:
USE pubs
SELECT DISTINCT city
FROM authors
WHERE city NOT IN
(SELECT city
FROM publishers)
Best Advice
I have a table containing 65 fields. One of the fields is a varchar(6) field
which is unique and contains a client number . witin the 64 other fields
there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if a
given pin exists
ie check if '123456' exists in PIN1, PIN2 - PIN10
any suggestionsDo all the pin columns have different values? Or is one of them populated
and the rest of them NULL? And is '123456' the client number in this case?
On 3/12/05 3:37 PM, in article
A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com, "Peter Newman"
<PeterNewman@.discussions.microsoft.com> wrote:
> I have a table containing 65 fields. One of the fields is a varchar(6) fie
ld
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Peter,
If there is no business-related distinction beween what is in PIn1, from
what is in Pin2 - Pin 10, i.e., if it does not matter where ValueA is in Pin
1
and ValueB in Pin2, or the other way around, then what you have is a simple
list of Pins associated with the parent record... (which s a client, I take
it)
If this is true, then you might want to read up on database normalization
concepts somewhere. The *Right* way to store these data items would be in
another table. If you had the data that way, this query, (as well as man
y
others) would be much simpler... not to mention a whole hos of other
issues...
"Peter Newman" wrote:
> I have a table containing 65 fields. One of the fields is a varchar(6) fie
ld
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Sorry Aaron, The PIN numbers will be different or Null. There will always b
e
a PIN1 value but from PIN2 - PIN10 may be Null's
'123456' is the pin number i am s
ing in this case ane you '111111' the
client number
Thanks
"Aaron [SQL Server MVP]" wrote:
> Do all the pin columns have different values? Or is one of them populated
> and the rest of them NULL? And is '123456' the client number in this case
?
>
>
> On 3/12/05 3:37 PM, in article
> A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com, "Peter Newman"
> <PeterNewman@.discussions.microsoft.com> wrote:
>
>|||Hi
You may want to try something like:
CREATE FUNCTION dbo.fn_IsNew( @.ClientName VARCHAR(20), @.PIN CHAR(6))
RETURNS INT
AS
BEGIN
IF EXISTS ( SELECT 1 FROM Cushion WHERE ClientName = @.ClientName
AND ( PIN1 = @.PIN OR PIN2 = @.PIN OR PIN3 = @.PIN OR PIN4 = @.PIN OR PIN5 =
@.PIN OR PIN6 = @.PIN OR PIN7 = @.PIN OR PIN8 = @.PIN OR PIN9 = @.PIN OR PIN10 =
@.PIN ) )
RETURN 1
RETURN 0
END
CREATE TABLE Cushion ( ClientName VARCHAR(20) NOT NULL,
PIN char(6) NOT NULL ,
PIN1 char(6),
PIN2 char(6),
PIN3 char(6),
PIN4 char(6),
PIN5 char(6),
PIN6 char(6),
PIN7 char(6),
PIN8 char(6),
PIN9 char(6),
PIN10 char(6),
CONSTRAINT IsNew CHECK ( dbo.fn_IsNew(ClientName ,PIN) = 0 )
)
INSERT INTO Cushion ( ClientName, PIN, PIN1, PIN2 )
VALUES ( 'ABC', '123456', '234567', '345678' )
INSERT INTO Cushion ( ClientName, PIN, PIN1, PIN2 )
VALUES ( 'DEF', '123456', '123456', '345678' )
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'IsNew'. The
conflict occurred in database 'Needlepoint', table 'Cushion'.
The statement has been terminated.
*/
Normalising the structure would make your queries easier, but you may want
to try something like:
CREATE VIEW NormalisedCushion AS
SELECT ClientName, PIN1 AS OldPin FROM Cushion
UNION ALL SELECT ClientName, PIN2 FROM Cushion
UNION ALL SELECT ClientName, PIN3 FROM Cushion
UNION ALL SELECT ClientName, PIN4 FROM Cushion
UNION ALL SELECT ClientName, PIN5 FROM Cushion
UNION ALL SELECT ClientName, PIN6 FROM Cushion
UNION ALL SELECT ClientName, PIN7 FROM Cushion
UNION ALL SELECT ClientName, PIN8 FROM Cushion
UNION ALL SELECT ClientName, PIN9 FROM Cushion
UNION ALL SELECT ClientName, PIN10 FROM Cushion
SELECT * FROM NormalisedCushion
WHERE OldPin = '234567'
John
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com...
>I have a table containing 65 fields. One of the fields is a varchar(6)
>field
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
> a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Okay, can you provide DDL, sample data, and desired results. See
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:0A679D44-51D8-45D5-8737-C4791812C84B@.microsoft.com...
> Sorry Aaron, The PIN numbers will be different or Null. There will always
be
> a PIN1 value but from PIN2 - PIN10 may be Null's
> '123456' is the pin number i am sing in this case ane you '111111' the
> client number
> Thanks
> "Aaron [SQL Server MVP]" wrote:
>
populated
case?
field
fields
check if a
which is unique and contains a client number . witin the 64 other fields
there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if a
given pin exists
ie check if '123456' exists in PIN1, PIN2 - PIN10
any suggestionsDo all the pin columns have different values? Or is one of them populated
and the rest of them NULL? And is '123456' the client number in this case?
On 3/12/05 3:37 PM, in article
A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com, "Peter Newman"
<PeterNewman@.discussions.microsoft.com> wrote:
> I have a table containing 65 fields. One of the fields is a varchar(6) fie
ld
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Peter,
If there is no business-related distinction beween what is in PIn1, from
what is in Pin2 - Pin 10, i.e., if it does not matter where ValueA is in Pin
1
and ValueB in Pin2, or the other way around, then what you have is a simple
list of Pins associated with the parent record... (which s a client, I take
it)
If this is true, then you might want to read up on database normalization
concepts somewhere. The *Right* way to store these data items would be in
another table. If you had the data that way, this query, (as well as man
y
others) would be much simpler... not to mention a whole hos of other
issues...
"Peter Newman" wrote:
> I have a table containing 65 fields. One of the fields is a varchar(6) fie
ld
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Sorry Aaron, The PIN numbers will be different or Null. There will always b
e
a PIN1 value but from PIN2 - PIN10 may be Null's
'123456' is the pin number i am s
ing in this case ane you '111111' theclient number
Thanks
"Aaron [SQL Server MVP]" wrote:
> Do all the pin columns have different values? Or is one of them populated
> and the rest of them NULL? And is '123456' the client number in this case
?
>
>
> On 3/12/05 3:37 PM, in article
> A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com, "Peter Newman"
> <PeterNewman@.discussions.microsoft.com> wrote:
>
>|||Hi
You may want to try something like:
CREATE FUNCTION dbo.fn_IsNew( @.ClientName VARCHAR(20), @.PIN CHAR(6))
RETURNS INT
AS
BEGIN
IF EXISTS ( SELECT 1 FROM Cushion WHERE ClientName = @.ClientName
AND ( PIN1 = @.PIN OR PIN2 = @.PIN OR PIN3 = @.PIN OR PIN4 = @.PIN OR PIN5 =
@.PIN OR PIN6 = @.PIN OR PIN7 = @.PIN OR PIN8 = @.PIN OR PIN9 = @.PIN OR PIN10 =
@.PIN ) )
RETURN 1
RETURN 0
END
CREATE TABLE Cushion ( ClientName VARCHAR(20) NOT NULL,
PIN char(6) NOT NULL ,
PIN1 char(6),
PIN2 char(6),
PIN3 char(6),
PIN4 char(6),
PIN5 char(6),
PIN6 char(6),
PIN7 char(6),
PIN8 char(6),
PIN9 char(6),
PIN10 char(6),
CONSTRAINT IsNew CHECK ( dbo.fn_IsNew(ClientName ,PIN) = 0 )
)
INSERT INTO Cushion ( ClientName, PIN, PIN1, PIN2 )
VALUES ( 'ABC', '123456', '234567', '345678' )
INSERT INTO Cushion ( ClientName, PIN, PIN1, PIN2 )
VALUES ( 'DEF', '123456', '123456', '345678' )
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'IsNew'. The
conflict occurred in database 'Needlepoint', table 'Cushion'.
The statement has been terminated.
*/
Normalising the structure would make your queries easier, but you may want
to try something like:
CREATE VIEW NormalisedCushion AS
SELECT ClientName, PIN1 AS OldPin FROM Cushion
UNION ALL SELECT ClientName, PIN2 FROM Cushion
UNION ALL SELECT ClientName, PIN3 FROM Cushion
UNION ALL SELECT ClientName, PIN4 FROM Cushion
UNION ALL SELECT ClientName, PIN5 FROM Cushion
UNION ALL SELECT ClientName, PIN6 FROM Cushion
UNION ALL SELECT ClientName, PIN7 FROM Cushion
UNION ALL SELECT ClientName, PIN8 FROM Cushion
UNION ALL SELECT ClientName, PIN9 FROM Cushion
UNION ALL SELECT ClientName, PIN10 FROM Cushion
SELECT * FROM NormalisedCushion
WHERE OldPin = '234567'
John
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:A13152B1-6807-4282-8D2A-1035B7A4DAB7@.microsoft.com...
>I have a table containing 65 fields. One of the fields is a varchar(6)
>field
> which is unique and contains a client number . witin the 64 other fields
> there are 10 'PIN number' fields ( PIN1, PIN2 etc ) and i need to check if
> a
> given pin exists
> ie check if '123456' exists in PIN1, PIN2 - PIN10
> any suggestions|||Okay, can you provide DDL, sample data, and desired results. See
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:0A679D44-51D8-45D5-8737-C4791812C84B@.microsoft.com...
> Sorry Aaron, The PIN numbers will be different or Null. There will always
be
> a PIN1 value but from PIN2 - PIN10 may be Null's
> '123456' is the pin number i am s
ing in this case ane you '111111' the> client number
> Thanks
> "Aaron [SQL Server MVP]" wrote:
>
populated
case?
field
fields
check if a
Subscribe to:
Posts (Atom)