Database Unit Testing: design

Showing posts with label design. Show all posts

Thursday, March 22, 2012

database size and design questions

I have a question regarding database design and size .
I am migrating from several DB2 databases to SQL server. I was going to
create different databases based on application dept or business units
(the way it has been in db2). But my application folks says, they
cannot connect to multiple database or join tables accross databases,
so have all the tables in one database.
If I do that( i hate to do it), the database size will easily be 200 -
250 GB.
1. Is having all the tables in 1 database a good idea, what are the
pros and cons ?
2. If I create this huge database, how can i do maintainance on it ? Is
there a way, I can backup quickly. My guestimate for backing up a 250
GB database is around 1-2 hrs, which is not feasible.
Any input is greatly appreciated.
Thanks
Roger1st of all, your application folks dont know what they'r talking about.
You CAN do multi-db joins, and they should be able to connect to multiple
DBs. (are they writing in VB, C++, C#, VB.NET, etc or what ?)
If they dont know how to do that, then they might want to go take a class or
something as it's pretty "101" stuff.
Multiple databases on the same server is not a bad option at all.
further, you should look at this site and learn about large Databases and
maintenance, etc:
Cheers
Greg Jackson
PDX, Oregon|||man I was so aggravated, I forgot to paste my link
http://www.microsoft.com/sql/techin...scalability.asp
GAJ|||Thanks Greg, Those guys are coding in COBOL, using ES-MTO (a
microfocus engine)- this is a mainframe conversion project. I showed
them that you can add the database name in front of the table name to
do multi database queries (am i right) . But they keep saying they
cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Thanks for link Greg...i appreciate it|||> cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Ideally, their external code would call stored procedures. Then they don't
have to know how you implement the database side. It could be one database
or 200, and you could have a job switch it back and forth between the two
architectures every other Thursday.
Let the developers write the code. This is why you have database people on
staff. :-)|||AMEN My Brother...!
Furthermore if they use ADO.NET and SQL Providers, they can do all the joins
they need.
but I'm not going to even gonna go down that road.
I like Aarons solution much better anyway.
GAJ|||"sql rookie" <anytasks@.gmail.com> wrote in message
news:1112294686.285175.58040@.o13g2000cwo.googlegroups.com...
> 2. If I create this huge database, how can i do maintainance on it ? Is
> there a way, I can backup quickly. My guestimate for backing up a 250
> GB database is around 1-2 hrs, which is not feasible.
Since no on addressed this:
Backup time should not generally be the criteria here. Recovery time should
be.
As you can do online backups, you can do backups w/o downtime.
Moreover, you can do other things to help with recovery.
Look at filegroup backups... i.e. backup only parts of the DB and recover
parts as required. (BTW, SQL 2005 Enterprise handles this in a BEAUTIFUL
manner...)
Also look at perhaps a weekly full backup and then daily differentials with
transaction log backups as required.

> Any input is greatly appreciated.
> Thanks
> Roger
>

database size and design questions

I have a question regarding database design and size .
I am migrating from several DB2 databases to SQL server. I was going to
create different databases based on application dept or business units
(the way it has been in db2). But my application folks says, they
cannot connect to multiple database or join tables accross databases,
so have all the tables in one database.
If I do that( i hate to do it), the database size will easily be 200 -
250 GB.
1. Is having all the tables in 1 database a good idea, what are the
pros and cons ?
2. If I create this huge database, how can i do maintainance on it ? Is
there a way, I can backup quickly. My guestimate for backing up a 250
GB database is around 1-2 hrs, which is not feasible.
Any input is greatly appreciated.
Thanks
Roger
1st of all, your application folks dont know what they'r talking about.
You CAN do multi-db joins, and they should be able to connect to multiple
DBs. (are they writing in VB, C++, C#, VB.NET, etc or what ?)
If they dont know how to do that, then they might want to go take a class or
something as it's pretty "101" stuff.
Multiple databases on the same server is not a bad option at all.
further, you should look at this site and learn about large Databases and
maintenance, etc:
Cheers
Greg Jackson
PDX, Oregon
|||man I was so aggravated, I forgot to paste my link
http://www.microsoft.com/sql/techinf...calability.asp
GAJ
|||Thanks Greg, Those guys are coding in COBOL, using ES-MTO (a
microfocus engine)- this is a mainframe conversion project. I showed
them that you can add the database name in front of the table name to
do multi database queries (am i right) . But they keep saying they
cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Thanks for link Greg...i appreciate it
|||> cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Ideally, their external code would call stored procedures. Then they don't
have to know how you implement the database side. It could be one database
or 200, and you could have a job switch it back and forth between the two
architectures every other Thursday.
Let the developers write the code. This is why you have database people on
staff. :-)
|||AMEN My Brother...!
Furthermore if they use ADO.NET and SQL Providers, they can do all the joins
they need.
but I'm not going to even gonna go down that road.
I like Aarons solution much better anyway.
GAJ
|||"sql rookie" <anytasks@.gmail.com> wrote in message
news:1112294686.285175.58040@.o13g2000cwo.googlegro ups.com...
> 2. If I create this huge database, how can i do maintainance on it ? Is
> there a way, I can backup quickly. My guestimate for backing up a 250
> GB database is around 1-2 hrs, which is not feasible.
Since no on addressed this:
Backup time should not generally be the criteria here. Recovery time should
be.
As you can do online backups, you can do backups w/o downtime.
Moreover, you can do other things to help with recovery.
Look at filegroup backups... i.e. backup only parts of the DB and recover
parts as required. (BTW, SQL 2005 Enterprise handles this in a BEAUTIFUL
manner...)
Also look at perhaps a weekly full backup and then daily differentials with
transaction log backups as required.

> Any input is greatly appreciated.
> Thanks
> Roger
>

database size and design questions

I have a question regarding database design and size .
I am migrating from several DB2 databases to SQL server. I was going to
create different databases based on application dept or business units
(the way it has been in db2). But my application folks says, they
cannot connect to multiple database or join tables accross databases,
so have all the tables in one database.
If I do that( i hate to do it), the database size will easily be 200 -
250 GB.
1. Is having all the tables in 1 database a good idea, what are the
pros and cons ?
2. If I create this huge database, how can i do maintainance on it ? Is
there a way, I can backup quickly. My guestimate for backing up a 250
GB database is around 1-2 hrs, which is not feasible.
Any input is greatly appreciated.
Thanks
Roger1st of all, your application folks dont know what they'r talking about.
You CAN do multi-db joins, and they should be able to connect to multiple
DBs. (are they writing in VB, C++, C#, VB.NET, etc or what ?)
If they dont know how to do that, then they might want to go take a class or
something as it's pretty "101" stuff.
Multiple databases on the same server is not a bad option at all.
further, you should look at this site and learn about large Databases and
maintenance, etc:
Cheers
Greg Jackson
PDX, Oregon|||man I was so aggravated, I forgot to paste my link
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
GAJ|||Thanks Greg, Those guys are coding in COBOL, using ES-MTO (a
microfocus engine)- this is a mainframe conversion project. I showed
them that you can add the database name in front of the table name to
do multi database queries (am i right) . But they keep saying they
cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Thanks for link Greg...i appreciate it|||> cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Ideally, their external code would call stored procedures. Then they don't
have to know how you implement the database side. It could be one database
or 200, and you could have a job switch it back and forth between the two
architectures every other Thursday.
Let the developers write the code. This is why you have database people on
staff. :-)|||AMEN My Brother...!
Furthermore if they use ADO.NET and SQL Providers, they can do all the joins
they need.
but I'm not going to even gonna go down that road.
I like Aarons solution much better anyway.
GAJ|||"sql rookie" <anytasks@.gmail.com> wrote in message
news:1112294686.285175.58040@.o13g2000cwo.googlegroups.com...
> 2. If I create this huge database, how can i do maintainance on it ? Is
> there a way, I can backup quickly. My guestimate for backing up a 250
> GB database is around 1-2 hrs, which is not feasible.
Since no on addressed this:
Backup time should not generally be the criteria here. Recovery time should
be.
As you can do online backups, you can do backups w/o downtime.
Moreover, you can do other things to help with recovery.
Look at filegroup backups... i.e. backup only parts of the DB and recover
parts as required. (BTW, SQL 2005 Enterprise handles this in a BEAUTIFUL
manner...)
Also look at perhaps a weekly full backup and then daily differentials with
transaction log backups as required.
> Any input is greatly appreciated.
> Thanks
> Roger
>

Wednesday, March 21, 2012

Database Setup and Design

I have a couple questions I hope someone might be able to answer, or
rather point me in the correct direction.

I am an independant developer and I am working on a small CRM for small
businesses. Nothing fancy, but built with c# and the .NET framework.

In my study of other CRM's like Microsoft, Clarify, and Misc I have
noticed something things that I do not understand.

1. When using a database, are customers and the customers
cases/problems kept in seperate tables?

2. When logging notes and misc, how is text formatted/kept in the
database. I see notes inside cases that are formatted and keep the
formatting after the cases are written the the DB.

Thanks for any info, and I am sure I am asking more then simple
questions.Sorry had one other thought. Say if customers are tracking emails or
phone messages, is a seperate table setup for each call/email or would
there be a notes database with a related key #.|||In a relational database a table represents an entity - informally, a set of
things that are alike in the sense that they have a common set of
attributes. So one might expect to see one table for Customers, another
table for Cases, a table for Invoices, etc. Data in tables are related by
keys. The database design will usually be static once it is built - the
design may be changed if the business requirements change but it isn't
necessary or desirable to create new tables at runtime. Database architects
use a set of rules called Normal Forms to help determine how to model
entities in a database.

In a nutshell, those are some general principles. However, my experience is
that commercial software packages built on relational databases sometimes
tend to use those databases in ways that are very non-standard and peculiar
to that application. The usual assumptions don't always apply. I don't know
about any of the packages you mentioned though so I could be wrong.

For storing formatted text there are various options. As RTF or HTML in a
text column for example. Or as a formatted document stored as a binary
database object.

Hope this helps.

--
David Portas
SQL Server MVP
--|||On 2/8/05 6:32 PM, in article
1107905552.527746.228500@.o13g2000cwo.googlegroups. com, "cvillard"
<cvillard@.gmail.com> wrote:

> I have a couple questions I hope someone might be able to answer, or
> rather point me in the correct direction.
> I am an independant developer and I am working on a small CRM for small
> businesses. Nothing fancy, but built with c# and the .NET framework.
> In my study of other CRM's like Microsoft, Clarify, and Misc I have
> noticed something things that I do not understand.
> 1. When using a database, are customers and the customers
> cases/problems kept in seperate tables?
> 2. When logging notes and misc, how is text formatted/kept in the
> database. I see notes inside cases that are formatted and keep the
> formatting after the cases are written the the DB.
> Thanks for any info, and I am sure I am asking more then simple
> questions.

We have a several applications where we store the data with HTML tags so it
displays formatted on the web site.

-Greg|||Thank you both for the information, this is really helpful and I think
I have some good information and ideas to start with. Much Appreciated.

Thanks again,
Chucksql

Monday, March 19, 2012

Database security design with ASP.net and form-based authentication

I'm setting up an ASP.Net intranet application with a SQL Server 2000
database. The application uses form-based authentication which is supported
by the following tables: User, Role, UserRole (where each user is assigned
specific roles). The system will have several different roles and users can
belong to multiple roles. As an example, let's say I have the following
roles: data entry, guest/view only, admin, report viewer. I'm guessing now
the system will have about 20 unique users. I've figured out how to
implement the role-based part on ASP.Net, but I'm stuck trying to decide the
best way to secure my database tables and stored procedures.
We're on a Novell network, so I'm using SQL Server authentication. At it's
simplest, I could just have one login for my database and lock down all the
tables and stored procedures to that one login. I'd like to have the
security a little tighter, though, so that only users who belong to the
administrative role can access the administrative procedures, only data
entry members can access the data entry procedures, etc.
I've thought of the following scenarios, but none makes me happy:
1) Create a SQL Server login for each user of the application and assign
them to roles. Then lock the tables and procedures down to the appropriate
roles.
I don't want to do this because I want an administrative user to be able to
create new application users through the Web application. This wouldn't be
possible as I don't have rights to create new SQL Server logins. I'd have
to go to my DB Admin each time we want to add a new user, which isn't really
acceptable.
2) Use SQL application roles to secure tables and procedures. We've used
these in other applications, but I'd like to stay away from them since
connection pooling doesn't work with them.
3) Use a set number of SQL Logins for each pre-defined role (data entry,
guest, admin, report viewer) and grant those logins permission to tables and
procedures as appropriate. I think this is my favorite method right now,
but then I'm not sure how to manage the multiple usernames and passwords.
Where do I store them and how does the application decide which one to use?
This is where maybe this question is more appropriate in an ASP.Net group,
but I thought I'd try here first.
I'm wondering what other people have done in this scenario?
Thanks,
Diane Y.Since you already have forms-based security, why not use a single SQL login
for all database access?
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I'm setting up an ASP.Net intranet application with a SQL Server 2000
> database. The application uses form-based authentication which is
> supported
> by the following tables: User, Role, UserRole (where each user is assigned
> specific roles). The system will have several different roles and users
> can
> belong to multiple roles. As an example, let's say I have the following
> roles: data entry, guest/view only, admin, report viewer. I'm guessing
> now
> the system will have about 20 unique users. I've figured out how to
> implement the role-based part on ASP.Net, but I'm stuck trying to decide
> the
> best way to secure my database tables and stored procedures.
> We're on a Novell network, so I'm using SQL Server authentication. At
> it's
> simplest, I could just have one login for my database and lock down all
> the
> tables and stored procedures to that one login. I'd like to have the
> security a little tighter, though, so that only users who belong to the
> administrative role can access the administrative procedures, only data
> entry members can access the data entry procedures, etc.
> I've thought of the following scenarios, but none makes me happy:
> 1) Create a SQL Server login for each user of the application and assign
> them to roles. Then lock the tables and procedures down to the
> appropriate
> roles.
> I don't want to do this because I want an administrative user to be able
> to
> create new application users through the Web application. This wouldn't
> be
> possible as I don't have rights to create new SQL Server logins. I'd have
> to go to my DB Admin each time we want to add a new user, which isn't
> really
> acceptable.
> 2) Use SQL application roles to secure tables and procedures. We've used
> these in other applications, but I'd like to stay away from them since
> connection pooling doesn't work with them.
> 3) Use a set number of SQL Logins for each pre-defined role (data entry,
> guest, admin, report viewer) and grant those logins permission to tables
> and
> procedures as appropriate. I think this is my favorite method right now,
> but then I'm not sure how to manage the multiple usernames and passwords.
> Where do I store them and how does the application decide which one to
> use?
> This is where maybe this question is more appropriate in an ASP.Net group,
> but I thought I'd try here first.
> I'm wondering what other people have done in this scenario?
> Thanks,
> Diane Y.
>|||That's actually the way I have it setup now and it's what I've mostly done
in the past. I just really liked how, when I used multiple application
roles, I was able to give only certain roles permission to certain stored
procedures. So, I was just wondering what others have done...
Diane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> Since you already have forms-based security, why not use a single SQL
login
> for all database access?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
> news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
assigned[vbcol=seagreen]
have[vbcol=seagreen]
used[vbcol=seagreen]
now,[vbcol=seagreen]
passwords.[vbcol=seagreen]
group,[vbcol=seagreen]
>|||> So, I was just wondering what others have done...
I usually opt for option #1 (individual logins/database role membership) for
intranet apps, . This allows SQL Server to control security from both
within and outside your application. Unfortunately, this isn't an option
for you due to the reasons you stated.
Application roles vs. role-based logins are similar approaches. These work
well when a user belongs to a single role so that you can use the same
security context for a given user's database access. However, this method
is problematic in your case because a user can belong to multiple roles
(cumulative permissions). The difficult question is how you decide which
database security context to enable when a user belongs to multiple roles
and multiple roles are associated with a particular application feature.
For example, if user Mary belongs to both DataEntry and ReportViewer roles
and your security is such that either role can view a report, which role
should be used as the database security context?
As long as you can define your business rules for identifying the
appropriate database security context, the implementation is easy. All you
need to do is store the application role name or login along with the
password (encrypted) in your Role table. You can then use that for database
access.
IMHO, the single login approach is best in your situation since you don't
want DBA involvement for security administration.
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:e8WPEH5QGHA.2300@.TK2MSFTNGP11.phx.gbl...
> That's actually the way I have it setup now and it's what I've mostly done
> in the past. I just really liked how, when I used multiple application
> roles, I was able to give only certain roles permission to certain stored
> procedures. So, I was just wondering what others have done...
> Diane
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> login
> assigned
> have
> used
> now,
> passwords.
> group,
>

Database schema design

Hi All,
First of all, please excuse me for the long post. The post is long but
hopefully the problem described is fairly simple for somewhat experienced
data modellers.
I am a beginner in database design so I was hoping that maybe you can give
me some advice on how to design a schema for the following situation:
I have 8 sensors; each sensor has a bunch of parameters and I would like to
store all this information in a SQL database.
The information is organized the following way:
Each sensor is identified by an "Index" and its data parameters by
"SubIndexes". For example, Sensor 1 could be: Index 1001. Let's say that all
sensors have two pieces of data associated with them "Speed" and
"Threshold"; As such, let's say that the "Speed" for sensor 1 is 5000 and
"Threshold" is 10000. These two pieces of data, as mentioned before, are
described by "SubIndexes", so in this case, SubIndex 1 would have a value of
5000 and SubIndex 2 would have a value of 10000. Thus, Sensor 1 could be
described the following way:
Index 1001
SubIndex1 5000
SubIndex2 10000
As I mentioned before, I will have 8 sensors, so the whole information would
be (w/ some example values) like this:
Index: 1001
SubIndex1: 5000
SubIndex2: 10000
Index: 1002
SubIndex1: 3000
SubIndex2: 17000
...
Index: 1008
SubIndex1: 2500
SubIndex2: 20000
The challening part is that I also have to store several "configurations" of
the system where each configuration could have the sensors hold different
data. For example:
Config 1:
Index: 1001
SubIndex1: 2000
SubIndex2: 3000
...
Config 2:
Index: 1001
SubIndex1: 1000
SubIndex2: 7000
Please note that the number of available configurations is not known at
design time.
What is the best way to model this? What I came up with doesn't seem the
most efficient. This is something that I thought it would work:
Create a table that has the following columns: Index, SubIndex, Data,
ConfigNo.
For example:
Index SubIndex Data ConfigNo.
1001 1 2000 1
1001 2 3000 1
...
1001 1 1000 2
1001 2 7000 2
..
It seems as though I am repeating all the "indexes" and "subindexes" when
these stay "constant" and only the data and configuration number changes. Is
there any way to specify all the indexes and subindexes (all sensors) one
time and to somehow make them "point" to various data given a certain
configuration number? Or what would be a better method of doing it? I know
all about primary/foreign key relationships but I still couldn't find a way
to efficiently store this data.
Thank you for your time!I have changed the schema a little bit, and now I have it the following way:
I have two tables:
Table 1:
SensorID Index
1 1001
2 1002
...
8 1008
Table 2:
SensorID SubIndex Value ConfigNo.
1 1 7000 1
1 2 10000 1
2 1 1000 1
2 2 5000 1
...
1 1 2000 2
...
This way, at least I tried to "normalize" the data so that if the "Index" of
a sensor changes, I have to only change it in one place (in Table 1.) I am
still not sure if I can make further improvements to this schema.
Thanks again!
"vvf" <novvfspam@.hotmail.com> wrote in message
news:uOB35kgMHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Hi All,
> First of all, please excuse me for the long post. The post is long but
> hopefully the problem described is fairly simple for somewhat experienced
> data modellers.
> I am a beginner in database design so I was hoping that maybe you can give
> me some advice on how to design a schema for the following situation:
> I have 8 sensors; each sensor has a bunch of parameters and I would like
to
> store all this information in a SQL database.
> The information is organized the following way:
> Each sensor is identified by an "Index" and its data parameters by
> "SubIndexes". For example, Sensor 1 could be: Index 1001. Let's say that
all
> sensors have two pieces of data associated with them "Speed" and
> "Threshold"; As such, let's say that the "Speed" for sensor 1 is 5000 and
> "Threshold" is 10000. These two pieces of data, as mentioned before, are
> described by "SubIndexes", so in this case, SubIndex 1 would have a value
of
> 5000 and SubIndex 2 would have a value of 10000. Thus, Sensor 1 could be
> described the following way:
> Index 1001
> SubIndex1 5000
> SubIndex2 10000
> As I mentioned before, I will have 8 sensors, so the whole information
would
> be (w/ some example values) like this:
> Index: 1001
> SubIndex1: 5000
> SubIndex2: 10000
> Index: 1002
> SubIndex1: 3000
> SubIndex2: 17000
> ...
> Index: 1008
> SubIndex1: 2500
> SubIndex2: 20000
> The challening part is that I also have to store several "configurations"
of
> the system where each configuration could have the sensors hold different
> data. For example:
> Config 1:
> Index: 1001
> SubIndex1: 2000
> SubIndex2: 3000
> ...
> Config 2:
> Index: 1001
> SubIndex1: 1000
> SubIndex2: 7000
> Please note that the number of available configurations is not known at
> design time.
> What is the best way to model this? What I came up with doesn't seem the
> most efficient. This is something that I thought it would work:
> Create a table that has the following columns: Index, SubIndex, Data,
> ConfigNo.
> For example:
> Index SubIndex Data ConfigNo.
> 1001 1 2000 1
> 1001 2 3000 1
> ...
> 1001 1 1000 2
> 1001 2 7000 2
> ..
> It seems as though I am repeating all the "indexes" and "subindexes" when
> these stay "constant" and only the data and configuration number changes.
Is
> there any way to specify all the indexes and subindexes (all sensors) one
> time and to somehow make them "point" to various data given a certain
> configuration number? Or what would be a better method of doing it? I know
> all about primary/foreign key relationships but I still couldn't find a
way
> to efficiently store this data.
> Thank you for your time!
>|||I'd think you'd want something more like:
Table Configs
Config SensorId Parm1 Parm2
1 1001 123 456
1 1002 234 567
...
2 1001 987 654
2 1002 876 543
...
Table Samples
SampleId SensorId Value
1 1001 1.3
1 1002 -7.2
...
Table SampleIdConfig
SampleId Config
1 7
2 3939
...
Or you could at small cost eliminate the last table and just add a
config column to the Samples table, it requires denormalizing the
column, but it's not like it ever changes!
J.
On Sat, 6 Jan 2007 22:16:10 -0500, "vvf" <novvfspam@.hotmail.com>
wrote:
>I have changed the schema a little bit, and now I have it the following way:
>I have two tables:
>Table 1:
>SensorID Index
> 1 1001
> 2 1002
> ...
> 8 1008
>Table 2:
>SensorID SubIndex Value ConfigNo.
> 1 1 7000 1
> 1 2 10000 1
> 2 1 1000 1
> 2 2 5000 1
> ...
> 1 1 2000 2
> ...
>This way, at least I tried to "normalize" the data so that if the "Index" of
>a sensor changes, I have to only change it in one place (in Table 1.) I am
>still not sure if I can make further improvements to this schema.
>Thanks again!
>"vvf" <novvfspam@.hotmail.com> wrote in message
>news:uOB35kgMHHA.4376@.TK2MSFTNGP03.phx.gbl...
>> Hi All,
>> First of all, please excuse me for the long post. The post is long but
>> hopefully the problem described is fairly simple for somewhat experienced
>> data modellers.
>> I am a beginner in database design so I was hoping that maybe you can give
>> me some advice on how to design a schema for the following situation:
>> I have 8 sensors; each sensor has a bunch of parameters and I would like
>to
>> store all this information in a SQL database.
>> The information is organized the following way:
>> Each sensor is identified by an "Index" and its data parameters by
>> "SubIndexes". For example, Sensor 1 could be: Index 1001. Let's say that
>all
>> sensors have two pieces of data associated with them "Speed" and
>> "Threshold"; As such, let's say that the "Speed" for sensor 1 is 5000 and
>> "Threshold" is 10000. These two pieces of data, as mentioned before, are
>> described by "SubIndexes", so in this case, SubIndex 1 would have a value
>of
>> 5000 and SubIndex 2 would have a value of 10000. Thus, Sensor 1 could be
>> described the following way:
>> Index 1001
>> SubIndex1 5000
>> SubIndex2 10000
>> As I mentioned before, I will have 8 sensors, so the whole information
>would
>> be (w/ some example values) like this:
>> Index: 1001
>> SubIndex1: 5000
>> SubIndex2: 10000
>> Index: 1002
>> SubIndex1: 3000
>> SubIndex2: 17000
>> ...
>> Index: 1008
>> SubIndex1: 2500
>> SubIndex2: 20000
>> The challening part is that I also have to store several "configurations"
>of
>> the system where each configuration could have the sensors hold different
>> data. For example:
>> Config 1:
>> Index: 1001
>> SubIndex1: 2000
>> SubIndex2: 3000
>> ...
>> Config 2:
>> Index: 1001
>> SubIndex1: 1000
>> SubIndex2: 7000
>> Please note that the number of available configurations is not known at
>> design time.
>> What is the best way to model this? What I came up with doesn't seem the
>> most efficient. This is something that I thought it would work:
>> Create a table that has the following columns: Index, SubIndex, Data,
>> ConfigNo.
>> For example:
>> Index SubIndex Data ConfigNo.
>> 1001 1 2000 1
>> 1001 2 3000 1
>> ...
>> 1001 1 1000 2
>> 1001 2 7000 2
>> ..
>> It seems as though I am repeating all the "indexes" and "subindexes" when
>> these stay "constant" and only the data and configuration number changes.
>Is
>> there any way to specify all the indexes and subindexes (all sensors) one
>> time and to somehow make them "point" to various data given a certain
>> configuration number? Or what would be a better method of doing it? I know
>> all about primary/foreign key relationships but I still couldn't find a
>way
>> to efficiently store this data.
>> Thank you for your time!
>>
>|||On Sat, 6 Jan 2007 22:13:13 -0500, "vvf" <novvfspam@.hotmail.com>
wrote:
>Create a table that has the following columns: Index, SubIndex, Data,
>ConfigNo.
>For example:
>Index SubIndex Data ConfigNo.
>1001 1 2000 1
>1001 2 3000 1
>...
>1001 1 1000 2
>1001 2 7000 2
Index is a bad choice of name, it is a reserved word in SQL. From the
given data I would be inclined to call it Sensor. Likewise SubIndex
sounds like it would better be named Measure, and for Data I might be
inclined toward Value. These are small things, but they are worth
getting right as early in the process as possible.
That structure looks fine to me, as far as I can say from the given
data. It has a three part key (Index, SubIndex, ConfigNo), and one
column of data.
Does SubIndex have the same meaning across all values of Index? If
SubIndex 1 is Speed for Index 1001, does that mean it is Speed for any
other Index that has a SubIndex of 1? In general it would be a VERY
good idea to maintain that sort of consistency if measures are common
across multiple indexes.
Does ConfigNo have any meaning across multiple values of Index? Is
someone going to say "now for ConfigNo = 7, show me each Index and the
associated Data"?
Any time I have seen sensor data in a database there has been a time
dimension somewhere. As I read your description I kept wondering when
it would show up.
Roy Harvey
Beacon Falls, CT|||Thanks JXStern.
I should have mentioned that the sensors could have as many as 255
subindexes (or params as you call them.)
The problem is that a table would have to have quite a few columns to go
with the schema that you proposed. I wasn't sure if this would pose a
problem in terms of efficiency or if it is even possible to have more than
255 columns in SQL Mobile (I should check books online for that.)
Thanks again,
vvf.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:snp0q2p4akk3k2mc7jj2ja2sc6tjjkuvkc@.4ax.com...
> I'd think you'd want something more like:
> Table Configs
> Config SensorId Parm1 Parm2
> 1 1001 123 456
> 1 1002 234 567
> ...
> 2 1001 987 654
> 2 1002 876 543
> ...
> Table Samples
> SampleId SensorId Value
> 1 1001 1.3
> 1 1002 -7.2
> ...
> Table SampleIdConfig
> SampleId Config
> 1 7
> 2 3939
> ...
>
> Or you could at small cost eliminate the last table and just add a
> config column to the Samples table, it requires denormalizing the
> column, but it's not like it ever changes!
> J.
>
> On Sat, 6 Jan 2007 22:16:10 -0500, "vvf" <novvfspam@.hotmail.com>
> wrote:
> >I have changed the schema a little bit, and now I have it the following
way:
> >
> >I have two tables:
> >
> >Table 1:
> >
> >SensorID Index
> > 1 1001
> > 2 1002
> > ...
> > 8 1008
> >
> >Table 2:
> >
> >SensorID SubIndex Value ConfigNo.
> > 1 1 7000 1
> > 1 2 10000 1
> > 2 1 1000 1
> > 2 2 5000 1
> > ...
> > 1 1 2000 2
> > ...
> >
> >This way, at least I tried to "normalize" the data so that if the "Index"
of
> >a sensor changes, I have to only change it in one place (in Table 1.) I
am
> >still not sure if I can make further improvements to this schema.
> >
> >Thanks again!
> >
> >"vvf" <novvfspam@.hotmail.com> wrote in message
> >news:uOB35kgMHHA.4376@.TK2MSFTNGP03.phx.gbl...
> >> Hi All,
> >>
> >> First of all, please excuse me for the long post. The post is long but
> >> hopefully the problem described is fairly simple for somewhat
experienced
> >> data modellers.
> >>
> >> I am a beginner in database design so I was hoping that maybe you can
give
> >> me some advice on how to design a schema for the following situation:
> >>
> >> I have 8 sensors; each sensor has a bunch of parameters and I would
like
> >to
> >> store all this information in a SQL database.
> >>
> >> The information is organized the following way:
> >>
> >> Each sensor is identified by an "Index" and its data parameters by
> >> "SubIndexes". For example, Sensor 1 could be: Index 1001. Let's say
that
> >all
> >> sensors have two pieces of data associated with them "Speed" and
> >> "Threshold"; As such, let's say that the "Speed" for sensor 1 is 5000
and
> >> "Threshold" is 10000. These two pieces of data, as mentioned before,
are
> >> described by "SubIndexes", so in this case, SubIndex 1 would have a
value
> >of
> >> 5000 and SubIndex 2 would have a value of 10000. Thus, Sensor 1 could
be
> >> described the following way:
> >>
> >> Index 1001
> >> SubIndex1 5000
> >> SubIndex2 10000
> >>
> >> As I mentioned before, I will have 8 sensors, so the whole information
> >would
> >> be (w/ some example values) like this:
> >>
> >> Index: 1001
> >> SubIndex1: 5000
> >> SubIndex2: 10000
> >>
> >> Index: 1002
> >> SubIndex1: 3000
> >> SubIndex2: 17000
> >>
> >> ...
> >>
> >> Index: 1008
> >> SubIndex1: 2500
> >> SubIndex2: 20000
> >>
> >> The challening part is that I also have to store several
"configurations"
> >of
> >> the system where each configuration could have the sensors hold
different
> >> data. For example:
> >>
> >> Config 1:
> >>
> >> Index: 1001
> >> SubIndex1: 2000
> >> SubIndex2: 3000
> >>
> >> ...
> >>
> >> Config 2:
> >> Index: 1001
> >> SubIndex1: 1000
> >> SubIndex2: 7000
> >>
> >> Please note that the number of available configurations is not known at
> >> design time.
> >>
> >> What is the best way to model this? What I came up with doesn't seem
the
> >> most efficient. This is something that I thought it would work:
> >>
> >> Create a table that has the following columns: Index, SubIndex, Data,
> >> ConfigNo.
> >>
> >> For example:
> >>
> >> Index SubIndex Data ConfigNo.
> >> 1001 1 2000 1
> >> 1001 2 3000 1
> >> ...
> >> 1001 1 1000 2
> >> 1001 2 7000 2
> >> ..
> >>
> >> It seems as though I am repeating all the "indexes" and "subindexes"
when
> >> these stay "constant" and only the data and configuration number
changes.
> >Is
> >> there any way to specify all the indexes and subindexes (all sensors)
one
> >> time and to somehow make them "point" to various data given a certain
> >> configuration number? Or what would be a better method of doing it? I
know
> >> all about primary/foreign key relationships but I still couldn't find a
> >way
> >> to efficiently store this data.
> >>
> >> Thank you for your time!
> >>
> >>
> >
>|||Hi Roy,
Thank you for your detailed answer. Please see comments embedded below:
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:rvp1q2lju44nfsfoc8e1kiu222cgcjkil6@.4ax.com...
> On Sat, 6 Jan 2007 22:13:13 -0500, "vvf" <novvfspam@.hotmail.com>
> wrote:
> >Create a table that has the following columns: Index, SubIndex, Data,
> >ConfigNo.
> >
> >For example:
> >
> >Index SubIndex Data ConfigNo.
> >1001 1 2000 1
> >1001 2 3000 1
> >...
> >1001 1 1000 2
> >1001 2 7000 2
> Index is a bad choice of name, it is a reserved word in SQL.
Correct. I had to resort to OLEDB to rename the column as I would get an
error trying to run SQL commands against this type of schema.
> From the
> given data I would be inclined to call it Sensor. Likewise SubIndex
> sounds like it would better be named Measure, and for Data I might be
> inclined toward Value. These are small things, but they are worth
> getting right as early in the process as possible.
Right; In my real schema, I have "Value" instead of "Data" and "SensorIndex"
instead of "Index" now. I "imported" the terminology (i.e., "Index",
"SubIndex") from the communications protocol "CANOPEN" as this is what we
use in our systems and had to have some sort of "consistency" throughout our
design.
> That structure looks fine to me, as far as I can say from the given
> data. It has a three part key (Index, SubIndex, ConfigNo), and one
> column of data.
> Does SubIndex have the same meaning across all values of Index? If
> SubIndex 1 is Speed for Index 1001, does that mean it is Speed for any
> other Index that has a SubIndex of 1?
Yes. All the SubIndexes have the exact same meaning across sensors. For
example, "SubIndex1" is "Speed" for all 8 sensors.
> In general it would be a VERY
> good idea to maintain that sort of consistency if measures are common
> across multiple indexes.
> Does ConfigNo have any meaning across multiple values of Index? Is
> someone going to say "now for ConfigNo = 7, show me each Index and the
> associated Data"?
Yes, they could say that. Basically, it is saving various configurations of
the sensors. For example, I could set up my sensors so that Sensor 1 has
SubIndex1(Speed) set to 10 and SubIndex2(Threshold) set to 20 and Sensor 2
to have SubIndex1(Speed) set to 30 and SubIndex2(Threshold) set to 40. Then,
I would save this "scenario" as ConfigNo "1". After that, I could change the
settings for these two sensors to something else and then save that
"scenario" to ConfigNo "2". Later, based on what ConfigNo I load, my sensors
would be filled with the corresponding values saved previously. Of course,
in this example I only showed 2 sensors while in the real scenario I would
have 8 sensors set to certain data.
> Any time I have seen sensor data in a database there has been a time
> dimension somewhere. As I read your description I kept wondering when
> it would show up.
The sensor data shown here is more like "configuration" data; In other
words, it is "static" data in a way because it only defines how the sensors
should behave when acquiring their data. That particular data (acquired
data) is saved in a different table and indeed has a time associated with
it. The data that we talked about here only configures the sensors so they
act in a certain way when acquiring data, and, as I mentioned earlier, I
have to have various pre-defined configurations for them.
Thanks again for your answer and analysis!
vvf.|||Your responses confirm to me that the design still looks good. The
only other comment I have is that you should be sure to have "master"
tables for Index, SubIndex and Configuration, even if the data is no
more than a key and description. But I expect you already have done
that.
Roy Harvey
Beacon Falls, CT
On Sun, 7 Jan 2007 18:24:08 -0500, "vvf" <novvfspam@.hotmail.com>
wrote:
>Hi Roy,
>Thank you for your detailed answer. Please see comments embedded below:
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>news:rvp1q2lju44nfsfoc8e1kiu222cgcjkil6@.4ax.com...
>> On Sat, 6 Jan 2007 22:13:13 -0500, "vvf" <novvfspam@.hotmail.com>
>> wrote:
>> >Create a table that has the following columns: Index, SubIndex, Data,
>> >ConfigNo.
>> >
>> >For example:
>> >
>> >Index SubIndex Data ConfigNo.
>> >1001 1 2000 1
>> >1001 2 3000 1
>> >...
>> >1001 1 1000 2
>> >1001 2 7000 2
>> Index is a bad choice of name, it is a reserved word in SQL.
>Correct. I had to resort to OLEDB to rename the column as I would get an
>error trying to run SQL commands against this type of schema.
>> From the
>> given data I would be inclined to call it Sensor. Likewise SubIndex
>> sounds like it would better be named Measure, and for Data I might be
>> inclined toward Value. These are small things, but they are worth
>> getting right as early in the process as possible.
>Right; In my real schema, I have "Value" instead of "Data" and "SensorIndex"
>instead of "Index" now. I "imported" the terminology (i.e., "Index",
>"SubIndex") from the communications protocol "CANOPEN" as this is what we
>use in our systems and had to have some sort of "consistency" throughout our
>design.
>> That structure looks fine to me, as far as I can say from the given
>> data. It has a three part key (Index, SubIndex, ConfigNo), and one
>> column of data.
>> Does SubIndex have the same meaning across all values of Index? If
>> SubIndex 1 is Speed for Index 1001, does that mean it is Speed for any
>> other Index that has a SubIndex of 1?
>Yes. All the SubIndexes have the exact same meaning across sensors. For
>example, "SubIndex1" is "Speed" for all 8 sensors.
>> In general it would be a VERY
>> good idea to maintain that sort of consistency if measures are common
>> across multiple indexes.
>
>> Does ConfigNo have any meaning across multiple values of Index? Is
>> someone going to say "now for ConfigNo = 7, show me each Index and the
>> associated Data"?
>Yes, they could say that. Basically, it is saving various configurations of
>the sensors. For example, I could set up my sensors so that Sensor 1 has
>SubIndex1(Speed) set to 10 and SubIndex2(Threshold) set to 20 and Sensor 2
>to have SubIndex1(Speed) set to 30 and SubIndex2(Threshold) set to 40. Then,
>I would save this "scenario" as ConfigNo "1". After that, I could change the
>settings for these two sensors to something else and then save that
>"scenario" to ConfigNo "2". Later, based on what ConfigNo I load, my sensors
>would be filled with the corresponding values saved previously. Of course,
>in this example I only showed 2 sensors while in the real scenario I would
>have 8 sensors set to certain data.
>> Any time I have seen sensor data in a database there has been a time
>> dimension somewhere. As I read your description I kept wondering when
>> it would show up.
>The sensor data shown here is more like "configuration" data; In other
>words, it is "static" data in a way because it only defines how the sensors
>should behave when acquiring their data. That particular data (acquired
>data) is saved in a different table and indeed has a time associated with
>it. The data that we talked about here only configures the sensors so they
>act in a certain way when acquiring data, and, as I mentioned earlier, I
>have to have various pre-defined configurations for them.
>Thanks again for your answer and analysis!
>vvf.
>|||On Sun, 7 Jan 2007 18:06:51 -0500, "vvf" <novvfspam@.hotmail.com>
wrote:
>Thanks JXStern.
>I should have mentioned that the sensors could have as many as 255
>subindexes (or params as you call them.)
Hmm. Well, for now I'd say just go for it, 255 columns. You could
further normalize by breaking them into a table keyed by config,
sensorid, and parm#, but that's probably overkill. Depending on what
you do with the data downstream, it's probably more efficient just to
have the 255 columns. Such natural sets of columns are formally a
"repeating group" which is forbidden in even 1NF, ... BUT frequently
kept together anyway, AS LONG AS THEY CONSTITUTE A SET, that is,
PARM#1 is not really the same thing as PARM#2.
>The problem is that a table would have to have quite a few columns to go
>with the schema that you proposed. I wasn't sure if this would pose a
>problem in terms of efficiency or if it is even possible to have more than
>255 columns in SQL Mobile (I should check books online for that.)
>Thanks again,
>vvf.
>
>"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
>news:snp0q2p4akk3k2mc7jj2ja2sc6tjjkuvkc@.4ax.com...
>> I'd think you'd want something more like:
>> Table Configs
>> Config SensorId Parm1 Parm2
>> 1 1001 123 456
>> 1 1002 234 567
>> ...
>> 2 1001 987 654
>> 2 1002 876 543
>> ...
>> Table Samples
>> SampleId SensorId Value
>> 1 1001 1.3
>> 1 1002 -7.2
>> ...
>> Table SampleIdConfig
>> SampleId Config
>> 1 7
>> 2 3939
>> ...
>>
>> Or you could at small cost eliminate the last table and just add a
>> config column to the Samples table, it requires denormalizing the
>> column, but it's not like it ever changes!
>> J.
>>
>> On Sat, 6 Jan 2007 22:16:10 -0500, "vvf" <novvfspam@.hotmail.com>
>> wrote:
>> >I have changed the schema a little bit, and now I have it the following
>way:
>> >
>> >I have two tables:
>> >
>> >Table 1:
>> >
>> >SensorID Index
>> > 1 1001
>> > 2 1002
>> > ...
>> > 8 1008
>> >
>> >Table 2:
>> >
>> >SensorID SubIndex Value ConfigNo.
>> > 1 1 7000 1
>> > 1 2 10000 1
>> > 2 1 1000 1
>> > 2 2 5000 1
>> > ...
>> > 1 1 2000 2
>> > ...
>> >
>> >This way, at least I tried to "normalize" the data so that if the "Index"
>of
>> >a sensor changes, I have to only change it in one place (in Table 1.) I
>am
>> >still not sure if I can make further improvements to this schema.
>> >
>> >Thanks again!
>> >
>> >"vvf" <novvfspam@.hotmail.com> wrote in message
>> >news:uOB35kgMHHA.4376@.TK2MSFTNGP03.phx.gbl...
>> >> Hi All,
>> >>
>> >> First of all, please excuse me for the long post. The post is long but
>> >> hopefully the problem described is fairly simple for somewhat
>experienced
>> >> data modellers.
>> >>
>> >> I am a beginner in database design so I was hoping that maybe you can
>give
>> >> me some advice on how to design a schema for the following situation:
>> >>
>> >> I have 8 sensors; each sensor has a bunch of parameters and I would
>like
>> >to
>> >> store all this information in a SQL database.
>> >>
>> >> The information is organized the following way:
>> >>
>> >> Each sensor is identified by an "Index" and its data parameters by
>> >> "SubIndexes". For example, Sensor 1 could be: Index 1001. Let's say
>that
>> >all
>> >> sensors have two pieces of data associated with them "Speed" and
>> >> "Threshold"; As such, let's say that the "Speed" for sensor 1 is 5000
>and
>> >> "Threshold" is 10000. These two pieces of data, as mentioned before,
>are
>> >> described by "SubIndexes", so in this case, SubIndex 1 would have a
>value
>> >of
>> >> 5000 and SubIndex 2 would have a value of 10000. Thus, Sensor 1 could
>be
>> >> described the following way:
>> >>
>> >> Index 1001
>> >> SubIndex1 5000
>> >> SubIndex2 10000
>> >>
>> >> As I mentioned before, I will have 8 sensors, so the whole information
>> >would
>> >> be (w/ some example values) like this:
>> >>
>> >> Index: 1001
>> >> SubIndex1: 5000
>> >> SubIndex2: 10000
>> >>
>> >> Index: 1002
>> >> SubIndex1: 3000
>> >> SubIndex2: 17000
>> >>
>> >> ...
>> >>
>> >> Index: 1008
>> >> SubIndex1: 2500
>> >> SubIndex2: 20000
>> >>
>> >> The challening part is that I also have to store several
>"configurations"
>> >of
>> >> the system where each configuration could have the sensors hold
>different
>> >> data. For example:
>> >>
>> >> Config 1:
>> >>
>> >> Index: 1001
>> >> SubIndex1: 2000
>> >> SubIndex2: 3000
>> >>
>> >> ...
>> >>
>> >> Config 2:
>> >> Index: 1001
>> >> SubIndex1: 1000
>> >> SubIndex2: 7000
>> >>
>> >> Please note that the number of available configurations is not known at
>> >> design time.
>> >>
>> >> What is the best way to model this? What I came up with doesn't seem
>the
>> >> most efficient. This is something that I thought it would work:
>> >>
>> >> Create a table that has the following columns: Index, SubIndex, Data,
>> >> ConfigNo.
>> >>
>> >> For example:
>> >>
>> >> Index SubIndex Data ConfigNo.
>> >> 1001 1 2000 1
>> >> 1001 2 3000 1
>> >> ...
>> >> 1001 1 1000 2
>> >> 1001 2 7000 2
>> >> ..
>> >>
>> >> It seems as though I am repeating all the "indexes" and "subindexes"
>when
>> >> these stay "constant" and only the data and configuration number
>changes.
>> >Is
>> >> there any way to specify all the indexes and subindexes (all sensors)
>one
>> >> time and to somehow make them "point" to various data given a certain
>> >> configuration number? Or what would be a better method of doing it? I
>know
>> >> all about primary/foreign key relationships but I still couldn't find a
>> >way
>> >> to efficiently store this data.
>> >>
>> >> Thank you for your time!
>> >>
>> >>
>> >
>

Saturday, February 25, 2012

Database Relationships

Hi,

Im managing our companys database which has approx 250 tables in it.
It is a design i have inherited from the people who originally created it, and i'm just having a look at all the foreign key relationships in it.

The actually have not defined many of these relationships. I'm just working through it now and placing these in now.

My question is this: Being such a complex database, can you have too many relationship?
For example, columns 'product_ID','Customer_ID', 'Branch_ID', and 'User_ID' occur in around 50 tables each.

If I enforce all these relationships, is it going to have a negative impact on the database? Is there any other factors i should consider? Is there a limit to how many relationships you should enforce?

Any help would be appreciated.

Josh DolanIMO it would really depend on who is access the database and how...

Sometime enforcing foreign key contraints is going to cause more problems then they are going to solve... but it should be evaluated on a case per case basis...|||I don't have any problem with enforcing all the constraints i want to implement. I would rather deal with the problems that it may occur and have them implemented than nothing at all.

I'm just thinking from the performance and database design angle.
Is it going to degrade performance that much? And, it is good database design to enforce most if not all foreign keys?

Thanks,

Josh|||Providing the foriegn key contraints are valid then IMO you can do no harm in implementing them providing you are happy to deal with possible issues.

Performace may be affected but not to any real extent IMO.|||You don't have too many relationships. They are necessary to maintain the relational integrity of your data, and if you remove them and allow inconsistencies to develop in your data then the extra coding and admin you will need to do to account for the errors WILL slow down your system.

It is more likely that you have too many tables, not too many relationships. Unfortunately, if there is already an interface and/or reporting structure developed around this schema, you are probably stuck with it.

The next time your company is considering developing an important database, advise them to hire a professional database designer to help them. A database is as important to an application as a foundation is important to a house. When I had my home built, I didn't go buy a book at Home Depot and then start pouring concrete myself. I hired professionals.

blindman

Thursday, March 22, 2012

database size and design questions

database size and design questions

database size and design questions

Wednesday, March 21, 2012

Database Setup and Design

Monday, March 19, 2012

Database security design with ASP.net and form-based authentication

Database schema design

Database schema design

Database schema design

Saturday, February 25, 2012

Database Relationships

Database Unit Testing

Blog Archive

About Me