Database Environment Naming Production -vs- Development

I have been looking for some documentation that would support or reject
my opinion on Production -vs- Development naming conventions. I
believe that each environment should be housed on separate servers with
identical names, access, users, stored procs...... If you either
agree or disagree with this methodology, I would appreciate your input.

TIA,
BillWith the same access? In most situations I don't want developers to
have the same access to production as they have in development. I
pretty much never want my users to have access to development either.

Other than that, I would agree. I think that every item that you have
to change between development and production is one more item that may
get you when moving changes from development to production.

In my environments I usually have a QA or testing server as well. This
one will more closely mirror production so that moving changes from QA
to production involves no manual changes at all - everything is
automated. This helps to ensure that no unforeseen bugs get moved into
production as the result of an errant upgrade script. The upgrade
scripts are run on QA, which is identical to production at the start
(often synchronized by a backup/restore unless the data is sensitive or
too large for the QA server). If the upgrade scripts work successfully
on QA and testing the changes is successful then confidence is pretty
high that they will work correctly in production.

HTH,
-Tom.|||I agree with Thomas. You cannot / should not test on a Dev server. So
you need at least three environments as near identical as possible.
Developers don't get access to Production except to troubleshoot a
problem that you can't repro elsewhere (unfortunately that happens).

--
David Portas
SQL Server MVP
--|||Thank you all for your responses.
And the access does change for Developers, we grant SELECT access to
view potential data issues and we also have Production control to move
both Databases and Interface methods (Executables and Web) from
Development to Production. There has been a desire to create a three
tier type environment, as we cycle out Production equipment I am keeping
it for the Development environments, so in another year or so I should
have the equipment for the 'QA' level.

Thanks again,
Bill

*** Sent via Developersdex http://www.developersdex.com ***|||Bill Willyerd (bwillyerd@.dshs.wa.gov) writes:
> I have been looking for some documentation that would support or reject
> my opinion on Production -vs- Development naming conventions. I
> believe that each environment should be housed on separate servers with
> identical names, access, users, stored procs...... If you either
> agree or disagree with this methodology, I would appreciate your input.

If we were do that in our shop, we would have to have a load of servers!

More generally, it depends on what your situation is. The above could
be a good idea for in-house applications, where there is exactly one
production server. (We develop a product, and we have one development
environment and one test environment for each customer and for each
version in production, test and development. That's a lot of databases.)

I would say that the key point is that you have separate servers.
Testing on a second database on the production machine can lead
unpleasant incidents, because test reveals a query with a poor query
plan.

Wether the database should have the same name? Of course, it helps,
but what if you need more than one testing environment? One of our
customers at one point had 3-4 test databases, all for our application.
I can reveal that they did not have four test servers. Thus, it is a
good idea to make it easy to switch database in the application.

As for the same stored procedures etc, this is best achieved by having
a version-control system as the definition of your system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

If your servers have identical names then they can not be on the same
network!!

John

"Bill Willyerd" <bwillyerd@.dshs.wa.gov> wrote in message
news:1114176300.794189.215830@.g14g2000cwa.googlegr oups.com...
>I have been looking for some documentation that would support or reject
> my opinion on Production -vs- Development naming conventions. I
> believe that each environment should be housed on separate servers with
> identical names, access, users, stored procs...... If you either
> agree or disagree with this methodology, I would appreciate your input.
> TIA,
> Bill

Database design

Hi All,

I wanted the expert opinion out there in the use of foreign keys as
primary keys in a table. I am not very good at explaining this
concept, but I am going to try -

Let us say you have a parent/master table( Ex: purchase order) that
is generating number (primary key for the main table)using the seed
and increment specified. We need all the records of this table to be
in sequential order - i.e. we need all purchase orders to be in
sequence. Now there are two different types purchase orders different
enough to have entity/tables of their own. So what are the downsides
of using the primary key generated in the main table which would
normally be a foreign key to the child table, as the actual primary
key in the child tables.

Thanks

KRIf I am understanding correctly, you want to be able to take the PK
from the master table, and use it as the PK in the detail table as
well? It sounds like from your description that you have two purchase
order tables, correct? So again, if my assumption is correct, you
want to be able to use the PK from either master as the PK in the
detail table right?

If so, I think you'll have a problem with the detail table in that you
can have the same number come up for both master tables, thereby
inserting a row into the detail table that is a duplicate.

Can you concat something onto the insert into the detail? ie:
MasterTbl1 and MasterTbl2 send entries to Detail. MasterTbl1 sends a
3, and since it's from that table, it tacks a M1 onto the beginning or
M2 if it's the other table - M13 or M23

That would set up your detail table like this:
PK
M11
M12
M21
M22
M13
M23

but that doesn't solve your keeping them in order, so maybe you could
tack it on the end?
1M1
1M2
2M1
3M1
2M2
3M2

hth
M@.

On May 25, 12:18 pm, KR <kra...@.bastyr.eduwrote:

Quote:

Originally Posted by

Hi All,
>
I wanted the expert opinion out there in the use of foreign keys as
primary keys in a table. I am not very good at explaining this
concept, but I am going to try -
>
Let us say you have a parent/master table( Ex: purchase order) that
is generating number (primary key for the main table)using the seed
and increment specified. We need all the records of this table to be
in sequential order - i.e. we need all purchase orders to be in
sequence. Now there are two different types purchase orders different
enough to have entity/tables of their own. So what are the downsides
of using the primary key generated in the main table which would
normally be a foreign key to the child table, as the actual primary
key in the child tables.
>
Thanks
>
KR

|||KR (kraman@.bastyr.edu) writes:

Quote:

Originally Posted by

Let us say you have a parent/master table( Ex: purchase order) that
is generating number (primary key for the main table)using the seed
and increment specified. We need all the records of this table to be
in sequential order - i.e. we need all purchase orders to be in
sequence.

Do you permit gaps in the sequence? Since you talk about seed and
increment, I suspect that you are using IDENTITY. Beware that IDENTITY
is very likely to give you gaps, since if an INSERT fails, an IDENTITY
number is still consumed.

Quote:

Originally Posted by

Now there are two different types purchase orders different
enough to have entity/tables of their own. So what are the downsides
of using the primary key generated in the main table which would
normally be a foreign key to the child table, as the actual primary
key in the child tables.

So you would have

CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY,
...
go
CREATE TABLE BlackOrders(OrderID int NOT NULL
PRIMARY KEY REFERENCES Orders(OrderId),
...

go
CREATE TABLE WhiteOrders(OrderID int NOT NULL
PRIMARY KEY REFERENCES Orders(OrderId),
...

That's a perfectly normal design, and quite a common way to address
supertypes and subtypes.

Joe Celko has a twist to this:

CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY,
OrderColour char(3)
CHECK (OrderColour IN ('BLK', 'WHT'))
...
UNIQUE(OrderId, OrderColour
go
CREATE TABLE BlackOrders(OrderID int NOT NULL PRIMARY KEY,
OrderColour char(3)
DEFAULT 'BLK',
CHECK (OrderColour = 'BLK'),
...
FOREIGN KEY (OrderID, OrderColour)
REFERENCES Orders(OrderId, OrderColour),
...

In this way you also assures that BlackOrders really only have black
orders.

Note: since the hour is later, I'm tired at the end of the week etc,
I have left out constraint names. But I like to stress that best
practice is to name your constraints.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On May 25, 9:18 pm, KR <kra...@.bastyr.eduwrote:

Quote:

Originally Posted by

Hi All,
>
I wanted the expert opinion out there in the use of foreign keys as
primary keys in a table. I am not very good at explaining this
concept, but I am going to try -
>
Let us say you have a parent/master table( Ex: purchase order) that
is generating number (primary key for the main table)using the seed
and increment specified. We need all the records of this table to be
in sequential order - i.e. we need all purchase orders to be in
sequence. Now there are two different types purchase orders different
enough to have entity/tables of their own. So what are the downsides
of using the primary key generated in the main table which would
normally be a foreign key to the child table, as the actual primary
key in the child tables.
>
Thanks
>
KR

1. Your PK is increment and seeded . So you are using Identity. Note
that Identity can have gaps .
2. If you have two different Purchase Order tables , what you can do
create another table having a union of PK from two tables and in
detail table FK will be pointing to this union table
3. If you don't want to go through 2, you need to create a trigger on
your detail table to check existence of key|||> We need all the records [sic] of this table to be in sequential order <<

Rows are not records and tables have no ordering. That is physical
and applies to files, which do have records. You are still un-
learning file systems.

Quote:

Originally Posted by

Quote:

Originally Posted by

>i.e. we need all purchase orders to be in sequence. <<

That is a logical condition that implies you have no gaps in PO
numbers. This sequence can be either numeric or temporal. I would
assume numeric. The constraint for no gaps is that:

(SELECT MAX(order_nbr) - MIN(order_nbr) + 1 FROM PurchaseOrders)
= (SELECT COUNT (order_nbr) FROM PurchaseOrders)

In Standard SQL-92, this would be in a CHECK() constraint; SQL Server
has to use a TRIGGER or stored procedure.

Quote:

Originally Posted by

Quote:

Originally Posted by

>there are two different types purchase orders different enough to have entity/tables of their own. So what are the downsides of using the primary key generated in the main table [sic: referenced table] which would normally be a foreign key to the child table [sic: referencing table], as the actual primary key in the child tables [sic]. <<

The terms parent and child are from old network databases and imply a
pointer chain. RDBMS uses references rather than points. Subtle but
important differences!

Erland already showed you my trick for doing sub-classes in SQL.