SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Database Design
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 06/13/2005 :  16:27:20  Show Profile  Reply with Quote
Why 2 tables for the logical model Brett? The section (and subsection) domain could be enforced by simple check constraints. Physcially and practically I could see those domains being split out to tables, but logical I don't see why..

I had something like this in mind...

--Add CHECK for Area and Constituent domains..
create table StuffIt(Area char(1) NOT NULL, Constituent CHAR(1) NOT NULL
	, EntrySequence  INT NOT NULL CHECK (EntrySequence > 0)
	,PRIMARY KEY (Area, Constituent, EntrySequence))
GO 
CREATE TRIGGER trg_StuffIt_Hide
ON StuffIt
INSTEAD OF INSERT
AS
BEGIN
--Handles internal numbering
--Warning: May result in data lose! <EVIL LAUGH>
INSERT StuffIT (Area, Constituent, EntrySequence)
SELECT DISTINCT Area, Constituent, ISNULL((SELECT MAX(EntrySequence) FROM dbo.StuffIt S 
			WHERE S.Area = I.Area AND I.Constituent = S.Constituent),0)+1
FROM INSERTED I END
GO
INSERT StuffIT (Area, Constituent, EntrySequence)
	VALUES ('A','V',5)
INSERT StuffIT (Area, Constituent, EntrySequence)
	VALUES ('A','F',0)
INSERT StuffIT (Area, Constituent, EntrySequence)
	SELECT 'A','V',0
	UNION ALL
	SELECT 'B','F',1
--The DISTINCT in the trigger collapses this to just one entry of B,F ..
--ie You lose rows...
INSERT StuffIT (Area, Constituent, EntrySequence)
	SELECT 'B','F',2
	UNION ALL
	SELECT 'B','F',1
	UNION ALL
	SELECT 'A','C',1
GO
Select * from stuffit
go
drop table stuffit
go


As rockmoose said, a view for the pretty identifier is a good idea

DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/13/2005 :  16:47:25  Show Profile  Reply with Quote
Wouldn't you consider a country a separate logical entity from county?

I'm so confused.

And why would you jump through hoops when a FK would do just as nicely.

And how do you add new countries then?



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
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 06/13/2005 :  16:58:37  Show Profile  Reply with Quote
>>Wouldn't you consider a country a separate logical entity from county?
Yes and no.. I mean.. It depends :-)

>>And how do you add new countries then?
Add it to the domain.. As I said, practically a table for each domain might be the go for "easier" expansion but the poster seems pretty sure of the allowed values..

In the current geo-political environment, the last thing we need is more countries.. except maybe Daveland (TM).. "Where the good times roll"

DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 06/13/2005 :  17:05:40  Show Profile  Reply with Quote
>> As rockmoose said, a view for the pretty identifier is a good idea
Did I really say that ?,
I know I did think it and I was going to write it, but did I ?

I was also into the INSTEAD OF trigger idea, to keep track of the sequence.
I did not really understand what Brett did,
(Is the data really structured that way ... on file physically ... now ... , or was it an example?)
Anyway the results look a lot like the first example I posted (I think)

>> I'm so confused.
Me too.

I am waiting for further details on the business requirements from the poster.
BTW: http://sqljunkies.com/Forums/ShowPost.aspx?PostID=6555

rockmoose
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 06/13/2005 :  17:47:05  Show Profile  Reply with Quote
>>I know I did think it and I was going to write it, but did I ?

In Daveland, anything is possible..

DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 06/13/2005 :  17:55:00  Show Profile  Reply with Quote
>> In Daveland, anything is possible..


rockmoose
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/13/2005 :  18:00:27  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
I'm still waiting for the business requirements. What are we modelling? Saying "we need to number a bunch of records based on a country and a county/town/state/etc and start counting at 1, by 1, for each" isn't exactly a clear set of business rules.

Again, macca, forget "records". forget numbering. Forget tables, normalization, and everything else regarding sql. You need to be able to state WHAT you are modelling and WHY.

- Jeff
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000