General Design Question

Hey

I need to store something a little different in a DB and I was hoping one of
you guys might be able to help me.

Basically it represents a 'world'. I have an initial state and then I get
info like this...

27/11/03 17:21 Mary is born
27/11/03 17:21 Dave is born
27/11/03 17:22 Sean is born
27/11/03 17:23 Peter dies
27/11/03 17:23 Fred is born

I need to be able to run querys like this...

How many people are alive at 27/11/03 17:22
Who was born between 27/11/03 17:22 and 27/11/03 17:23
etc.

Problem is, I'm going to have hundres of 'world's each with thousands of
entrys.

All help is appreciated :)

Tnx

Naomi"Naomi Morton" <dopey_delete@.remove.iol.ie> wrote in message
news:1069954180.280897@.emeairlvalid.ie.baltimore.c om...
> Hey
> I need to store something a little different in a DB and I was hoping one of
> you guys might be able to help me.
> Basically it represents a 'world'. I have an initial state and then I get
> info like this...
> 27/11/03 17:21 Mary is born
> 27/11/03 17:21 Dave is born
> 27/11/03 17:22 Sean is born
> 27/11/03 17:23 Peter dies
> 27/11/03 17:23 Fred is born

Perhaps something like this:

CREATE TABLE Worlds
(
world_id INT NOT NULL PRIMARY KEY
)

CREATE TABLE Persons
(
world_id INT NOT NULL REFERENCES Worlds (world_id),
person_name VARCHAR(25) NOT NULL,
birth_datetime DATETIME NOT NULL,
death_datetime DATETIME NULL, -- NULL if still alive
CHECK (death_datetime >= birth_datetime),
PRIMARY KEY (world_id, birth_datetime, person_name) -- simplification
)

> I need to be able to run querys like this...
> How many people are alive at 27/11/03 17:22

DECLARE @.alive_at_datetime DATETIME
SET @.alive_at_datetime = '20031127 17:22'
SELECT world_id, COUNT(*) AS alive_at_datetime
FROM Persons
WHERE birth_datetime <= @.alive_at_datetime AND
(death_datetime IS NULL OR death_datetime > @.alive_at_datetime)
GROUP BY world_id

> Who was born between 27/11/03 17:22 and 27/11/03 17:23

DECLARE @.start_datetime DATETIME, @.end_datetime DATETIME
SET @.start_datetime = '20031127 17:22'
SET @.end_datetime = '20031127 17:23'
SELECT world_id, person_name, birth_datetime
FROM Persons
WHERE birth_datetime BETWEEN @.start_datetime AND @.end_datetime

> etc.
> Problem is, I'm going to have hundres of 'world's each with thousands of
> entrys.

Millions of rows should not present a problem at all.

Regards,
jag

> All help is appreciated :)
> Tnx
> Naomi|||> 27/11/03 17:21 Mary is born
> 27/11/03 17:21 Dave is born
> 27/11/03 17:22 Sean is born
> 27/11/03 17:23 Peter dies
> 27/11/03 17:23 Fred is born
> I need to be able to run querys like this...
> How many people are alive at 27/11/03 17:22
> Who was born between 27/11/03 17:22 and 27/11/03 17:23
> etc.

Hi Naomi,

What you have is similar to banking transaction data. For example,
27/11/03 17:21 customer #1 debited $100 from his checking account. In
this case, the entity in question are individual accounts.

I assume you're creating a fantasy gaming world. The entity in
question are the character "avatars". To make a long story short, you
should have a WORLD table and an AVATAR table. The avatar is
populated by your journal transaction entries and should have worldID,
avatarID, birth, and death columns.

To query how many are alive:
select count(*) from avatar where death < @.death or death is null and
worldID=@.worldID

To query who was born between @.start and @.end:
select * from avatar where worldID=@.worldID and birth between @.start
and @.end

-- Louis

General design efficiency question

My general question is whether there is anything to be gained by having 50 tables in one database versus 5 tables each in 10 databases.

I have a number of different databases running on a server (SQL Server 2k). The different databases represent different functional groups, for instance car maintenance, cab reservation/dispatch, cab accounting, limo reservation/dispatch, limo accounting, etc. There is some crossover, for instance the cab dispatch system would look to car maintenance to validate the car number entered.

A friend who happens to be IT Director at the local university suggested that the server would run more efficiently if there was only one database, rather than the roughly 12 I have now. His belief is that each separate database carries a certain amount of overhead, and combining them into one would be advantageous.

Is he all wet, or would there be gains to be made?

TIAThere would be gains. It is just a matter of whether you would notice them. I have never tested the scenario, so I have no actual numbers. Personally, I would favor the single database approach, for permissions administration reasons. A user only needs permissions on a stored procedure and not the underlying tables, IF all of the tables it accesses are in the same database. Same for views.

Now, suppose you want to restore the 10 databases back to a point in time just before your temp deleted a pile of data in "some tables". Do you want to do 10 separate restores, one?

I am sure some of the other folks here can come up with other examples, if they tried.|||He's right for the wrong reasons.

I don't think you would get a performance boost from combining the databases. You could make arguments for either increased efficiency or decreased efficiency either way.

But...from a data management standpoint it makes administrative sense to combine the databases if they reference eachother for lookup values or cross-database queries. From you limited description of the situation, I would recommend combining them.|||Thanks for your thoughts. I guess I'll look at going through the effort.|||I read the first line and fell off my barstoo...um office chair

My general question is whether there is anything to be gained by having 50 tables in one database versus 5 tables each in 10 databases.

Are you kidding? Ever hear of maintenance?

I have a number of different databases running on a server (SQL Server 2k). The different databases represent different functional groups, for instance car maintenance, cab reservation/dispatch, cab accounting, limo reservation/dispatch, limo accounting, etc. There is some crossover, for instance the cab dispatch system would look to car maintenance to validate the car number entered.

A friend who happens to be IT Director at the local university suggested that the server would run more efficiently if there was only one database, rather than the roughly 12 I have now. His belief is that each separate database carries a certain amount of overhead, and combining them into one would be advantageous.

Is he all wet, or would there be gains to be made?

TIA

I like the cut of his gib...are all the tables named differently? Also, what about the apps? Would they be hard to port?|||Putting objects in different databases will allow you more flexibility in terms of allocating data and log files. (IE if you have to use primary for select into reasons, you can more easily manage it with multiple databases). Also, depending on your backup requirements you may be able to set some databases to simple mode, some to full, only do trn backups for certain databases and the list goes on. I think it's more a management thing then a performance thing.

-kilka|||Putting objects in different databases will allow you more flexibility in terms of allocating data and log files. (IE if you have to use primary for select into reasons, you can more easily manage it with multiple databases). Also, depending on your backup requirements you may be able to set some databases to simple mode, some to full, only do trn backups for certain databases and the list goes on. I think it's more a management thing then a performance thing.

-kilka

Nope ... and thank you for playing (at least for the first statement). You can create as many segments as you desire for a single database for both data and log files ... non-clustered indexes on multiple disks, split log files, etc., etc., etc.

Not to mention that referential integrity rules can only be enforced with database, not across database.

Also, IMHO, cross database joins requires the engine to drill down thru the metadata of the other databases to access the index and page structures of the "foreign" database since SQLServer "cooks" it database space when it is allocated.

That being said, it would be interesting to see the results of an empirical test. If Paul Randal is still hanging around, maybe he can comment on this topic!|||What do you mean by "cooks" space when it's allocated?

General Database/Query and Form Design question

Currently working on an existing system written using an Access 2002 project
(.adp) and SQL Server 2000 and need to add some ehancements.
The system is a leasing system where a customer leases one or more assets
for a defined term (eg. 24, 36, 48 months...). Each lease may also be
associated with a fixed, or variable monthly repayment regime.
For example a customer wants to lease a Boat for 24 months for the first 12
months (period 1 - 12) they pay $50 per month, for period 13 - 18 they pay
$35 per month, and for the last 6 months (period 19 - 24) they pay $25 per
month.
The proposed table design is as follows:
tblLease
LeaseId int (identity) PK
CustomerId FK
TermId FK
...
tblLeaseAsset (1:M relationship to tblLease)
AssetId int PK
LeaseId int PK/FK to tblLease
AssetDescription
...
tblLeaseAssetRate (Intersection table - 1:M relationship to tblLeaseAsset,
1:M relationship to tblLeaseTerm)
AssetId int PK/FK
TermPeriodId int PK/FK
Payment decimal (19,4)
...
tblLeasePeriodTerm (1:M relationship to tblLease)
TermPeriodId int (identity) PK
LeaseId int FK to tblLease
FromTermPeriod small int
ToTermPeriod small int
...
Whilst the users are happy to enter the initial lease and period/term
information as a Parent/Main form and Child/Subform combination. They would
like to be able to enter the Asset and payment information together as a
single Child/Subform:
Period Period Period
Asset Description 1 - 12 13 - 18 19 - ...
A Boat $50 $35 $25
The problem is that this requires a pivot table/cross tabulation type view
of the data and these types of queries are not generally updatable.
Does anyone have any ideas how I might achieve the objective either in terms
of database, query or form design so that users can insert, update, delete
and view records?
Your assistance apreciated
Guy HortonWell, for each "asset", you got
AssetName AssetCost WherePurchased
Boat $15,000 WalMart
Car $5,000 MacDonalds
etc.
Now, just put another sub-form to the "right" of a above where you can enter
"many" values for each of the above.
If your cursor is in Boat, then you can enter:
Period Amount
1 - 12 $50
13 - 18 $35
etc.
I can think of "many" cases where you got a detail line, and need "many" for
that details. consider the QuickBooks when you write a single check, and a
split amount, you need to "split" out the funds to "many" values. So, the
solution is to make two side by side sub-forms.
Take a look at the following screen shots, and especially the last one where
I have a "classic" cheque "distribution" screen (for each check/person on
the left, I can enter "many" split values for that particlar amount
(donation in this example) on the right side...
http://www.members.shaw.ca/AlbertKa...ticles/Grid.htm
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.members.shaw.ca/AlbertKallal|||I'd strongly recommend Albert's way, but if you're absolutely forced to
doing it on the same line, you can try a few different things that I can
think of:
1. Play with Access' PivotTable features. I've never found them all that
useful, and very klunky to use, but it may get you where you're trying to
go. Don't ask me for more detailed info on how to do that, though, I
generally avoid PivotTables like the plague.
2. Implement a temporary table that goes across as you'd like it to, then
as each record is read/updated, transfer the values to/from the "real" table
in the OnCurrent and Before/AfterUpdate events.
3. Use an embedded control of some kind (Hierarchical FlexGrid?) to display
the data instead.
Good luck,
Rob
"Guy Horton" <guy.horton@.nospam.bigpond.com> wrote in message
news:%23RueVVwwFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Currently working on an existing system written using an Access 2002
> project (.adp) and SQL Server 2000 and need to add some ehancements.
> The system is a leasing system where a customer leases one or more assets
> for a defined term (eg. 24, 36, 48 months...). Each lease may also be
> associated with a fixed, or variable monthly repayment regime.
> For example a customer wants to lease a Boat for 24 months for the first
> 12 months (period 1 - 12) they pay $50 per month, for period 13 - 18 they
> pay $35 per month, and for the last 6 months (period 19 - 24) they pay $25
> per month.
> The proposed table design is as follows:
> tblLease
> LeaseId int (identity) PK
> CustomerId FK
> TermId FK
> ...
> tblLeaseAsset (1:M relationship to tblLease)
> AssetId int PK
> LeaseId int PK/FK to tblLease
> AssetDescription
> ...
> tblLeaseAssetRate (Intersection table - 1:M relationship to tblLeaseAsset,
> 1:M relationship to tblLeaseTerm)
> AssetId int PK/FK
> TermPeriodId int PK/FK
> Payment decimal (19,4)
> ...
> tblLeasePeriodTerm (1:M relationship to tblLease)
> TermPeriodId int (identity) PK
> LeaseId int FK to tblLease
> FromTermPeriod small int
> ToTermPeriod small int
> ...
> Whilst the users are happy to enter the initial lease and period/term
> information as a Parent/Main form and Child/Subform combination. They
> would like to be able to enter the Asset and payment information together
> as a single Child/Subform:
> Period Period Period
> Asset Description 1 - 12 13 - 18 19 - ...
> A Boat $50 $35 $25
> The problem is that this requires a pivot table/cross tabulation type view
> of the data and these types of queries are not generally updatable.
> Does anyone have any ideas how I might achieve the objective either in
> terms of database, query or form design so that users can insert, update,
> delete and view records?
> Your assistance apreciated
> Guy Horton
>|||Albert,
Thank you for your excellent response. I reviewed your article and screen
shots and have to say they look very professional.
I briefly considered side by side subforms and agree with you that this is a
very workable option, and probably the option I will go with. Although, it
doesn't allow the users to view all lease rates for all the currently
visible leased assets, and they think of periods as running across as
opposed to down the form.
Your thoughts appreciated.
Best Regards,
Guy
"Albert D.Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:ukKGPmwwFHA.460@.TK2MSFTNGP15.phx.gbl...
> Well, for each "asset", you got
>
> AssetName AssetCost WherePurchased
> Boat $15,000 WalMart
> Car $5,000 MacDonalds
> etc.
> Now, just put another sub-form to the "right" of a above where you can
> enter "many" values for each of the above.
> If your cursor is in Boat, then you can enter:
> Period Amount
> 1 - 12 $50
> 13 - 18 $35
> etc.
> I can think of "many" cases where you got a detail line, and need "many"
> for that details. consider the QuickBooks when you write a single check,
> and a split amount, you need to "split" out the funds to "many" values.
> So, the solution is to make two side by side sub-forms.
> Take a look at the following screen shots, and especially the last one
> where I have a "classic" cheque "distribution" screen (for each
> check/person on the left, I can enter "many" split values for that
> particlar amount (donation in this example) on the right side...
> http://www.members.shaw.ca/AlbertKa...ticles/Grid.htm
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.members.shaw.ca/AlbertKallal
>|||Robert,
Thank you for your response. I agree that Albert's solution is the probably
the most sensible way to go, and that PivotTable features are klunky to use.
Your thoughts appreciated
Guy
"Robert Morley" <rmorley@.magma.ca.no.freakin.spam> wrote in message
news:eYrGDpxwFHA.3756@.tk2msftngp13.phx.gbl...
> I'd strongly recommend Albert's way, but if you're absolutely forced to
> doing it on the same line, you can try a few different things that I can
> think of:
> 1. Play with Access' PivotTable features. I've never found them all that
> useful, and very klunky to use, but it may get you where you're trying to
> go. Don't ask me for more detailed info on how to do that, though, I
> generally avoid PivotTables like the plague.
> 2. Implement a temporary table that goes across as you'd like it to, then
> as each record is read/updated, transfer the values to/from the "real"
> table in the OnCurrent and Before/AfterUpdate events.
> 3. Use an embedded control of some kind (Hierarchical FlexGrid?) to
> display the data instead.
>
> Good luck,
> Rob
> "Guy Horton" <guy.horton@.nospam.bigpond.com> wrote in message
> news:%23RueVVwwFHA.2064@.TK2MSFTNGP09.phx.gbl...
>

Fuzzy lookup match issue

Hello,

I have a peculiar problem in my project. My project design is like this

The number in (...) are count of records.

File feed (1000)

|

|

Fuzzy Lookup

against Table2

|

|

Split Fz Lookup results

(_Similarity >= 0.60 && _Confidence >= 0.85)

| |

| |

| Write matches to Table1 (250)

|

Fuzzy Group

Remaining rows (750)

|

|

Split Fz Group results

| |

| |

Write Canonicals Write Dupes

to Table2 to Table1

(300) (450)

This is basically a customer de-dupification project.

The Table2 has the canonicals and Table1 has the dupes (of the canonicals).

I already have some data in these tables and the new data is matched against the existing data

in these tables and classified as new customers and duplicate customers.

In the above process one could notice that the rows identified as dupes of already exsting canonicals

by the Fuzzy Lookup task are written into the dupes table (Table1) and will not be processed further down

the line in the project.

But in my case I see that those matches identified by Fuzzy lookup are further being included in the

Fuzzy Grouping also.

When I run this in debug mode in BIDS, it shows the correct numbers as I have depicted in the

illustration above. But, after execution, when I query the tables it shows that all 1000 rows

went through Fuzzy Grouping.

Any thoughts?

Btw, is there anyway to upload attachments to the postings here?

I also tried introducing a Derived Column between the 'Split Fz Lookup Results' and 'Write matches to Table1' to write some string into one of the table columns. It did not.