Need help / suggestion

Hey guys

I have to implement a dynamic Parent - > Child Scenario ... but the catch is
as Follows :

I need to create this Table Design so that I can have multiple Parent ->
child - > parent Relationships

(ie Db driven "Window Explorer - feel". 1 Folder that holds another Folder
that holds another Folder etc to Infinite )

So if the Above makes any sense ... Suggestions would be Welcome

Thanx1) Get a copy of TREES & HIERARCHIES IN SQL for several different
methods

2) Google for "nested sets", "path enumeration" and "adjacency list"
models in SQL|||Thanx !!!

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109335451.349754.115330@.l41g2000cwc.googlegr oups.com...
> 1) Get a copy of TREES & HIERARCHIES IN SQL for several different
> methods
> 2) Google for "nested sets", "path enumeration" and "adjacency list"
> models in SQL|||Nice 1 celko !!! :P
http://www.intelligententerprise.co...equestid=315563

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109335451.349754.115330@.l41g2000cwc.googlegr oups.com...
> 1) Get a copy of TREES & HIERARCHIES IN SQL for several different
> methods
> 2) Google for "nested sets", "path enumeration" and "adjacency list"
> models in SQL

Need expert help in database design..

Hello friends,I have to design a database for banking application,but it is my first db design so i need some expert advice.

Data base has some performance constraint i.e high output ,minimum query time,need to process 2 to 5 million transaction per day.

Further specification can be revealed if appropriate help is provided.

your advice will be of great help for me.

Thx

Are you requesting a 'paid' consultant to help you create a cohesive design, or, are you 'hoping' that a bunch of volunteers will take their time and help you do something that you are getting paid to do?

You know the problems that can occur when something is 'designed by committee'...

Feel free to put out YOUR design considerations and get feedback -but having a group of 'anonymous yahoos' (present company included) do your design for you is heading for disaster.

|||

Thankyou for replying to my question.

Here we are trying to design a solution for " KYC(know your customer )/AML(anti money laundering)" .These two terms stems from

various compliance in banking sector, i.e Bank secrecy ACT ,US patriotic act(if you are from America) and various other compliance related to financial industry.

We need to create a monitoring mechanism on various financial transactions(2-5 million per day,minimum requirement) either in real time or in batch mode.

In this mechanism we will monitor transactions on various predefined rules and rules using techniques of data mining.

The data related to various transaction is intended to be collected using integration services of sql server 2005.

I am looking for some volunteer,but one thing I can assure u that i will need avice only for some key areas('ll not vex u too much) i.e preventing foolish mistakes which can lead to desaster at later stage and some high performance(high data retrival speed in large database) related issues.

I am more concerned about the performance issue related to data base, you can imagin how big a bank cuctomer's database can be and data related to their every day transaction.

Plz let me know if you are intrested or can advice me some resource related to experienced data base design.

For helping hand.

Thx once again.

Gagan

|||

You are very welcome to post your questions, proposals for 'debate', and things to ponder. Some here may contribute, depending upon their time and interest.

However, I think that perhaps this isn't the best forum. I would consider the [ Transact SQL ] or [ Database Engine ] since they seem to have more 'traffic' -translating into readers and possible contributors.

There are a couple of documents referenced in this thread: http://forums.microsoft.com/MSDN/showpost.aspx?postid=1436785&siteid=1

Good Luck. (This thread is now closed.)

sql

need design advice

we are desgning a db that will keep name and address information for all
kinds of entities: customers, employees, vendors, contacts, etc. Instead of
having N/A fields in each of the tables it seems like a good idea to have
one nameaddess table keyed by an identity int and have all of the other
tables reference this table via foreign key (either implicit or explicit).
What are the pros and cons of doing this kind of design?
Thanks,
T
Not sure what your referencing by an "N/A" field. However, if I'm
following you correctly, setting this up will depend if address can
have more than one entity (ie: one address can be a vendor and a
customer or something like that).
If not, then 2 tables should do it:
tbl_addresses
tbl_entities
the entities table will have "customers", "employees" etc. marked with
a PK (ie: "entity_id"). Then, of course, you'd have an "entity_id"
column in the addresses table that's a FK.
If an address can have more than one entity, then you can add a third
table that has three columns: the PK for the third table and 2 FK's
(one for addresses and one for entities). Here you wouldn't need the
FK in the "adresses" table.
So, let's say address #8 in the table has 1 entity and address #9 has
3, your 3rd table's data would something like:
ID ADDRESS_ID ENTITY_ID
1 8 2
2 9 2
3 9 4
4 9 5
and so on...
Of course, the "entity_id" shown above would refer to customers or
employees or whatever.
Hope that makes sense or helps in some way!
Jeremy

need design advice

we are desgning a db that will keep name and address information for all
kinds of entities: customers, employees, vendors, contacts, etc. Instead of
having N/A fields in each of the tables it seems like a good idea to have
one nameaddess table keyed by an identity int and have all of the other
tables reference this table via foreign key (either implicit or explicit).
What are the pros and cons of doing this kind of design?
Thanks,
TNot sure what your referencing by an "N/A" field. However, if I'm
following you correctly, setting this up will depend if address can
have more than one entity (ie: one address can be a vendor and a
customer or something like that).
If not, then 2 tables should do it:
tbl_addresses
tbl_entities
the entities table will have "customers", "employees" etc. marked with
a PK (ie: "entity_id"). Then, of course, you'd have an "entity_id"
column in the addresses table that's a FK.
If an address can have more than one entity, then you can add a third
table that has three columns: the PK for the third table and 2 FK's
(one for addresses and one for entities). Here you wouldn't need the
FK in the "adresses" table.
So, let's say address #8 in the table has 1 entity and address #9 has
3, your 3rd table's data would something like:
ID ADDRESS_ID ENTITY_ID
----
1 8 2
2 9 2
3 9 4
4 9 5
and so on...
Of course, the "entity_id" shown above would refer to customers or
employees or whatever.
Hope that makes sense or helps in some way!
Jeremysql

need design advice

we are desgning a db that will keep name and address information for all
kinds of entities: customers, employees, vendors, contacts, etc. Instead of
having N/A fields in each of the tables it seems like a good idea to have
one nameaddess table keyed by an identity int and have all of the other
tables reference this table via foreign key (either implicit or explicit).
What are the pros and cons of doing this kind of design?
Thanks,
TNot sure what your referencing by an "N/A" field. However, if I'm
following you correctly, setting this up will depend if address can
have more than one entity (ie: one address can be a vendor and a
customer or something like that).
If not, then 2 tables should do it:
tbl_addresses
tbl_entities
the entities table will have "customers", "employees" etc. marked with
a PK (ie: "entity_id"). Then, of course, you'd have an "entity_id"
column in the addresses table that's a FK.
If an address can have more than one entity, then you can add a third
table that has three columns: the PK for the third table and 2 FK's
(one for addresses and one for entities). Here you wouldn't need the
FK in the "adresses" table.
So, let's say address #8 in the table has 1 entity and address #9 has
3, your 3rd table's data would something like:
ID ADDRESS_ID ENTITY_ID
----
1 8 2
2 9 2
3 9 4
4 9 5
and so on...
Of course, the "entity_id" shown above would refer to customers or
employees or whatever.
Hope that makes sense or helps in some way!
Jeremy

Need another Opinion (Database Design)

I need some other opinions on whether or not this is considered a proper database design structure.

Here is the relationship...We have PEOPLE, that each can belong to a COMPANY.

PERSON_TABLE
Person_ID
Company_ID

COMPANY_TABLE
Company_ID

Then each person can trust other people of other companies, but can only trust 1 person per company.

My question is this. In order to maintain a constraint of 1 person per company, is it considered OK to add a the redundant column Company_ID to the PERSON_TRUSTED_TABLE(and then creating a composite primary key on the Person_ID/Company_ID) instead of just adding a trigger to the PERSON_TRUSTED_TABLE to uphold the constraint.

PERSON_TRUSTED_TABLE
Person_ID
Trusted_Company_ID
Trusted_Person_ID

I would appreciate anyone's opinion. Thanks so much!

There is a third option although it's not much different from your PK idea. I think the major questions is in how many other tables do you reference the person_truested_table? You need a PK in your table anyway, so if you don't create a composite PK out of (person_id, trusted_company_id), what would you do to create a PK?

1.) would you create a PK column?

In this case you could create a unique key on (person_id, trusted_company_id), which has the same effect as the PK and you don't need a trigger. This would be my personal preference if you reference this table by many others, simply because you have a 1 column PK.

2.) would you create the PK out of (person_id, truested_person_id)?

Well this in not much different from the (person_id, trusted_company_id) idea, so i would add the trusted_company_id column and create the PK on those as you suggested.

What i would not do at all for this problem is to create a trigger.|||

As I understand your issue, it seems that it is necessary to allow all any and all [Person] to trust any and all [Company] -constrained that any one person can ONLY trust one person from any one company.

If that seems about right, then a constraining composite key (PK or UI) in the [Person_Trusted] table is what is needed. That composite key 'should' be on [Person_ID] and [Trusted_Company_ID].

One person, Bill, ID: 25, is allowed to trust only one person for company Acme Industries, ID: 1001. It doens't matter which Acme Industries employee Bill has trusted, once he has a trust relationship with Acme Industires another cannot be allowed unless he first removes the existing relationship. Or he can just UPDATE the [Person_Trusted] table to reflect the replacement 'trusted' person.

As a side note: You don't need the archaic denotation of [_TABLE] on the table names. It will be obvious from the context that [Person], or [Company], or whatever is in fact, a table. And it is wasted keystrokes to have to continually type something that does not provide any value.

|||I understand what you are saying about the _TABLE thing. I just put it in the post to be more descriptive.

I get your point, but do you realize that you can get to the Trusted_Company_ID from the Trusted_Person_ID. So really, by putting the Trusted_Company_ID on the PERSON_TRUSTED_TABLE, it would be redundant data.

Instead of putting the Trusted_Company_ID on the PERSON_TRUSTED_TABLE, you could just create a trigger on that table that gets the Company_ID from the Trusted_Person_ID, and upholds the constraint. Therefore no redundant data.

After this explanation, do you still think that adding the redundant column Trusted_Company_ID to the PERSON_TRUSTED_TABLE is the correct implementation?

Thank you so much for the input. It is very helpful.|||

In my opinion, this is one of those worthy exceptions to 'normalized' data.

Yes, it is a 'little' redundent. However, anytime you manage the Trusted_Person, you will most likely have the Trusted_Company information at hand -so it is not a 'big deal'. Consider establishing [CASCADE UPDATES] and/or [CASCADE DELETES] as appropriate.

My consideration is that using a TRIGGER is a relatively heavy handed approach, adding significant effort to the server to manage what should be a 'simple' CONSTRAINT.

The effect you are seeking is that for any one Person there can be only one Company trusted (it doens't matter who the Trusted_Person is for that Company). Having to traverse through the Trusted_Person-Company to determine if there is already a record for that Person-Company could be unnecessary effort.

Need advise on horizontal table partitioning

Hello,

I need some advise in table design, and more specifically about table partitioning. I read some papers and there was mentioned that if a table is expected to hold millions of rows, it's a good idea to partition it.

Vertical partitioning, as I understood it, is separating data that differs in some way in a separate table, adding a key field as an identifier to what segment it belongs.

The particular table holds signal measurements from different sites and has the following layout :

SiteID int,
MeasID int,
Timestamp datetime,
Value real,
Status int
etcJunk int

SiteID, MeasID and Timestamp form the PK.

The horizontal partitioning should be obviously done by Timestamp, but that creates pretty bad problems with automatic table creation (when it's time to do so), inserts from measurement system and selects from report tools - generally with the application code.

I would appreciate any consistent strategy for implementing such partitioning.

Ops, just to mention - OS - Windows, MSSQL 2000, in case that matters in any way.An update...

After reading some stuff for partitions, views etc, I decided to split the data in two tables - one for the last month (that will need to be queried regularly), called RDATA_CURR and one for the the older data for the last year (that will be queried really rarely). A scheduled stored procedure should move data between them on daily basis. For the experiment I created a table RDATA_FULL that holds the sane data as the above tables. Also created a view (union all) on the first two tables. The thing is that there are some selects that need to be transparent upon the splitted tables and don't need to be really fast (while other are to be fast and always from the last month or so). The inserts are always at the current, because the timestamps are current time, actually.

So I generated few millions raws of junk and filled the tables. After that made the same typical select from the view, the full table and the current (the needed data is within the current period). The execution plan shows that actually select from the view is twice as slow as select from the full table, which is very close to the select from the current (full has millions and current has tens of thousands). This is not really what I expected - the split does not really make things better.

So my question is, first, does really the execution plan shows correctly queries cost? If it is, then such kind of splitting the data does not really improve performance on selects from all data.

The typical select is a select for a certain time interval for a certain ID. I wonder if I could make the select in the view more sophisticated, so the query is actually done on the partitioned table that actually holds the data.|||Well, it seems that I will drop the partitioning thing, especially with the results I get from very large tables.

There are two tables - rdata_f holding data for all sites, about 10 millions generated rows for various sites and rdata_1 (check constraint on site_id) holding data only from site 1, having about 2 millions rows. The tables have identical layout and PK (the only difference is CHECK on Site_ID). The thing is that selects on index are much faster with the larger table.

How is that possible? I use dbcc dropcleanbuffers before each select and the check constraint should not matter for selects. The larger table is 15 times faster...

Need advice on ETL architecture...

I am about to design (I think it could be considered as) ETL system and here
are my thoughts on how it should work at a very high level.
My intention in this post is to find out if there is a better way to design
this.
Data is originally entered in an as400 system.
On nightly basis, openquery from sql server is used to get all the tables
into "extract_database" as is from as400.
"User_database" takes data on nightly basis from "extract_database"
and converts data into user friendly tables. These tables could be
used by front end programmers to write tsql to drive the front end.
If we have a table called "Sales" then on nightly basis "Sales" table would
get refreshed from as400 and if there is a change in particluar sales order
then I want "user_database" to have the latest change in Sales table.
Some how I also want to document the change in Sales history. In other words
I don't want to lose any data change from day to day.
I also would like to have "history_database" which would keep track of
record changes or deletion.
In summary, "extract_database" has the copy of what exactly got imported,
"user_database" has the converted data so that front end application could
tap into it, and "history_database" is used for reporting.
For daily data refresh, I would like to daily keep track of data imported
for each table.
Are there any white papers, articles, web sites, books that address this
nightly database refresh design and best practices'
Please excuse me in advance if I missed some thing.
TIA..Read about the audit tools that you can buy to do this.|||What you are describing is a transactional data warehouse. The ETL (Extract
Transform Load) programming is just the tip of the iceburg.
"sqlster" <nospam@.nospam.com> wrote in message
news:AB2E77C4-8B79-4CC4-8B0D-2FF079239E1C@.microsoft.com...
>I am about to design (I think it could be considered as) ETL system and
>here
> are my thoughts on how it should work at a very high level.
> My intention in this post is to find out if there is a better way to
> design
> this.
> Data is originally entered in an as400 system.
> On nightly basis, openquery from sql server is used to get all the tables
> into "extract_database" as is from as400.
> "User_database" takes data on nightly basis from "extract_database"
> and converts data into user friendly tables. These tables could be
> used by front end programmers to write tsql to drive the front end.
> If we have a table called "Sales" then on nightly basis "Sales" table
> would
> get refreshed from as400 and if there is a change in particluar sales
> order
> then I want "user_database" to have the latest change in Sales table.
> Some how I also want to document the change in Sales history. In other
> words
> I don't want to lose any data change from day to day.
> I also would like to have "history_database" which would keep track of
> record changes or deletion.
> In summary, "extract_database" has the copy of what exactly got imported,
> "user_database" has the converted data so that front end application could
> tap into it, and "history_database" is used for reporting.
> For daily data refresh, I would like to daily keep track of data imported
> for each table.
> Are there any white papers, articles, web sites, books that address this
> nightly database refresh design and best practices'
> Please excuse me in advance if I missed some thing.
> TIA..
>

Need advice on Designing a VLDB OLTP Database

Hi,
I am trying to design a sql2005 Database with 4 tables of 35 million
rows. we need to update fields in one of the table by joining with
other three. Also we need to delete the roughly 2 million rows daily
from these tables as new data is added. Please suggest if breaking all
these tables into different databases is better or having them all in
one single database is better?. Also the joining keys are varchar
fields. So any suggestions on indexing?
thanks
KrisPartitioning is your friend.
If your daily data sets are in different partitions, the drop can be
metadata-only. That is very fast. Essentially, you are truncating the
partition. To make this work, you have to have indexes aligned with the
partitioning function. Read all about partitioning in BOL.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<krishnasingaraju@.gmail.com> wrote in message
news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
> Hi,
> I am trying to design a sql2005 Database with 4 tables of 35 million
> rows. we need to update fields in one of the table by joining with
> other three. Also we need to delete the roughly 2 million rows daily
> from these tables as new data is added. Please suggest if breaking all
> these tables into different databases is better or having them all in
> one single database is better?. Also the joining keys are varchar
> fields. So any suggestions on indexing?
> thanks
> Kris
>|||> Partitioning is your friend.
Yes, very well said (I like it). Also reference DPV's (Distributed Partition
Views).
In a DPV you create n databases and link them together with a view. While
I've heard of multiple partitions on one server and that t performed well, a
DPV can be split up onto multiple nodes of an active/active cluster. There
is one important point, which will be in BOL, make sure that the data is
arranged so that at least 80% of the data you need comes from one
partition/server, or performance could actually be worse.
Jay
> If your daily data sets are in different partitions, the drop can be
> metadata-only. That is very fast. Essentially, you are truncating the
> partition. To make this work, you have to have indexes aligned with the
> partitioning function. Read all about partitioning in BOL.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> <krishnasingaraju@.gmail.com> wrote in message
> news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
>> Hi,
>> I am trying to design a sql2005 Database with 4 tables of 35 million
>> rows. we need to update fields in one of the table by joining with
>> other three. Also we need to delete the roughly 2 million rows daily
>> from these tables as new data is added. Please suggest if breaking all
>> these tables into different databases is better or having them all in
>> one single database is better?. Also the joining keys are varchar
>> fields. So any suggestions on indexing?
>> thanks
>> Kris
>|||Given the size info, I'm not sure you really want to even consider DPV.
Linchi
"Jay" wrote:
> > Partitioning is your friend.
> Yes, very well said (I like it). Also reference DPV's (Distributed Partition
> Views).
> In a DPV you create n databases and link them together with a view. While
> I've heard of multiple partitions on one server and that t performed well, a
> DPV can be split up onto multiple nodes of an active/active cluster. There
> is one important point, which will be in BOL, make sure that the data is
> arranged so that at least 80% of the data you need comes from one
> partition/server, or performance could actually be worse.
> Jay
> > If your daily data sets are in different partitions, the drop can be
> > metadata-only. That is very fast. Essentially, you are truncating the
> > partition. To make this work, you have to have indexes aligned with the
> > partitioning function. Read all about partitioning in BOL.
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> >
> >
> > <krishnasingaraju@.gmail.com> wrote in message
> > news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
> >> Hi,
> >>
> >> I am trying to design a sql2005 Database with 4 tables of 35 million
> >> rows. we need to update fields in one of the table by joining with
> >> other three. Also we need to delete the roughly 2 million rows daily
> >> from these tables as new data is added. Please suggest if breaking all
> >> these tables into different databases is better or having them all in
> >> one single database is better?. Also the joining keys are varchar
> >> fields. So any suggestions on indexing?
> >>
> >> thanks
> >> Kris
> >>
> >
>
>

need advice

Hi guys,
Can you suggest good books on relational algebra and database design(NF)?
Thanks a lot in advance
Alexhttp://www.datamodel.org/
AMB
"Alex" wrote:

> Hi guys,
> Can you suggest good books on relational algebra and database design(NF)?
> Thanks a lot in advance
> Alex
>
>|||>> Can you suggest good books on relational algebra and database design(NF)?
One widely recognized book on the scientific & mathematical aspects (
algebra, calculus & deductive ) of relational theory is the Foundations of
Databases by Abiteboul, Hull & Vianu. The book is relatively advanced and
the ones starting out might find the book tough to comprehend. On the topic
of relational algebra and the coverage of its practical utility, no other
popular book does more justice more than C J Date's Introduction to Database
systems. As far as datamodelling is concerned there are several good books
around. Books by C J Date, Toby Teorey, David Maier etc are excellent
primers dealing with conceptual modelling and logical design.
As in every other field with its basis on science & mathematics, sometimes
it makes good sense to avoid product based design books and 10-minute guides
to learn data modelling & design.
Anith

need a way to secure RDL from being edited ?

Hi,
Is there absolutely no way at all that I can employ security on an RDL file
to stop users editing them ?
i.e design a report for a site, ship it, upload it to Rep Services (user on
site has admin right to machine - runs IIS and SQL internally). Is there no
way to stop someone opening the RDL in any RDL aware application and
amending it ?
Thanks for your time.
ScottScott,
You can easily secure the built in Report Manager web interface. Under
security properties for the Home directory, add your Windows login as a
Content Manager, then remove BUILTIN\Administrators. You can assign Browser
rights (and editing rights if you so desire) to the appropriate groups at
either the top level or lower down in the tree.
(I assume these users aren't accessing the RS web service directly from a
.NET application. That can be secured, but is a whole other ball of wax.)
Ted
"scott" wrote:
> Hi,
> Is there absolutely no way at all that I can employ security on an RDL file
> to stop users editing them ?
> i.e design a report for a site, ship it, upload it to Rep Services (user on
> site has admin right to machine - runs IIS and SQL internally). Is there no
> way to stop someone opening the RDL in any RDL aware application and
> amending it ?
> Thanks for your time.
> Scott
>
>|||Hi Ted,
Thanks for reply. I understand what you mean about securing the report and
dirs within the web interface but im more concerned about the actual RDL.
For example:
- I write a report and email it to a client
- they upload it into reporting services for users
- reporting services is running internally on IIS that also runs SQL server
- at least one admin user onsite will have admin rights to
sqlserver/iisserver/reportingservices machine.
If they have an RDL aware app they can amend the RDL. I would like to
secure the RDL from amending because:
1. it takes time and effort to write the RDL and we have a very complicated
db relationship structure.
2. we have to support the RDL files and if they have been amended it could
make it impossible.
Thanks for your time.