schema

How should I organize my master ddl script.

I am currently creating a master ddl for our database. Historically we have used backup/restore to version our database, and not maintained any ddl scripts. The schema is quite large. My current thinking: Break script into parts (possibly in separate scripts): table creation add indexes add triggers add constraints Each script wo...

Update database schema in Entity Framework

I installed VS SP1 and played around with Entity Framework. I created a schema from an existing database and tried some basic operations. Most of it went well, except the database schema update. I changed the database in every basic way: added a new table deleted a table added a new column to an existing table deleted a column from ...

Validation Patterns for Custom XML Documents

I have a web application that generates a medium sized XML dataset to be consumed by a third party. I thought it would be a good idea to provide some form of schema document for the XML that I generate so I pasted the XML into Visual Studio and got it to generate an XSD. The annoying thing is that my XML doesn't validate to the XSD that ...

XML Schema construct for "Any number of these elements - in any order"

I need to create an XML schema that looks something like this: <xs:element name="wrapperElement"> <xs:complexType> <xs:sequence> <xs:element type="el1"> <xs:element type="el2"> </xs:sequence> <xs:WhatGoesHere?> <xs:element type="el3"> <xs:element type="el4"> <xs:element type="el5"> ...

Migrating database changes from development to live

Perhaps the biggest risk in pushing new functionality to live lies with the database modifications required by the new code. In Rails, I believe they have 'migrations', in which you can programmatically make changes to your development host, and then make the same changes live along with the code that uses the revised schema. And roll bo...

Address book DB schema

I need to store contact information for users. I want to present this data on the page as an hCard and downloadable as a vCard. I'd also like to be able to search the database by phone number, email, etc. What do you think is the best way to store this data? Since users could have multiple addresses, etc complete normalization would be...

One or Two Primary Keys in Many-to-Many Table?

I have the following tables in my database that have a many-to-many relationship, which is expressed by a connecting table that has foreign keys to the primary keys of each of the main tables: Widget: WidgetID (PK), Title, Price User: UserID (PK), FirstName, LastName Assume that each User-Widget combination is unique. I can see two...

What's the best way to handle one-to-one relationships in SQL?

Let's say I've got Alpha things that may or may not be or be related to Bravo or Charlie things. These are one-to-one relationships: No Alpha will relate to more than one Bravo. And no Bravo will relate to more than one Alpha. I've got a few goals: a system that's easy to learn and maintain. data integrity enforced within my databas...

reload a .sql schema without restarting mysqld

Is it possible to reload a schema file without having to restart mysqld? I am working in just one db in a sea of many and would like to have my changes refreshed without doing a cold-restart. ...

Storing email messages in a database

What sort of database schema would you use to store email messages, with as much header information as practical/possible, into a database? Assume that they have been fed into a script from the MTA and parsed into the relevant headers/body/attachments. Would you store the message body whole in the database table, or split any MIME-part...

XML Serialize boolean as 0 and 1

Hello Everybody. The XML Schema Part 2 specifies that an instance of a datatype that is defined as boolean can have the following legal literals {true, false, 1, 0}. The following XML, for example, when deserialized, sets the boolean property "Emulate" to true. <root> <emulate>1</emulate> </root> However, when I serialize the obj...

Ruby on Rails Migration - Create New Database Schema

Hi I have a migration that runs an SQL script to create a new Postgres schema. When creating a new database in Postgres by default it creates a schema called 'public', which is the main schema we use. The migration to create the new database schema seems to be working fine, however the problem occurs after the migration has run, when ra...

How can I maintain consistent DB schema accross 18 databases (sql server)?

We have 18 databases that should have identical schemas, but don't. In certain scenarios, a table was added to one, but not the rest. Or, certain stored procedures were required in a handful of databases, but not the others. Or, our DBA forgot to run a script to add views on all of the databases. What is the best way to keep database...

XML schema construct for "any one or more of these elements but must be at least one"

I'm trying to set up part of a schema that's like a "Sequence" where all child elements are optional, but at least one of the elements must be present, and there could be more than one of them. I tried doing the following, but XMLSpy complains that "The content model contains the elements <element name="DateConstant"> and <element name=...

Is it valid to have a 'choice' of 'group' elements when defining an XML Schema (XSD)

Is it valid to have a 'choice' or 'group' elements when defining an XML Schema (XSD) i.e. is the following valid <xs:complexType name="HeaderType"> <xs:sequence> <xs:element name="reservation-number" type="ReservationNumberType" minOccurs="1" maxOccurs="1" nillable="false" /> <xs:choice minOccurs="1" maxOccurs="1"> <xs:...

Is it possible in W3C's XML Schema language (XSD) to allow a series of elements to be in any order but still limit occurrences?

I know about all and choice, but they don't account for a case where I do want some elements to be able to occur more than once, such as: <Root> <ThingA/> <ThingB/> <ThingC/> <ThingC/> <ThingC/> </Root> I could use sequence, but I'd prefer to allow these children to be in any order. I could use any, but then I coul...

Tips on refactoring an outdated database schema

Being stuck with a legacy database schema that no longer reflects your data model is every developer's nightmare. Yet with all the talk of refactoring code for maintainability I have not heard much of refactoring outdated database schemas. What are some tips on how to transition to a better schema without breaking all the code that r...

Any good SQL Anywhere database schema comparison tools?

Are there any good database schema comparison tools out there that support Sybase SQL Anywhere version 10? I've seen a litany of them for SQL Server, a few for MySQL and Oracle, but nothing that supports SQL Anywhere correctly. I tried using DB Solo, but it turned all my non-unique indexes into unique ones, and I didn't see any optio...

Denormalize an XML schema programmatically

I need to take any given valid XML schema (XSD) and denormalize it to a simple form containing no refs, no includes, etc. All simple type definitions should be inline, such that when looking at any given element, all declarations are visible without performing another lookup. I've found some tools that have this built-in, but I need to...

Schema reference for IIS programmatic administration

Where can I find the IIS object schema? All I found from MSDN was a picture of the class hierarchy. To be clear, I want to configure IIS through either WMI or ADSI and I'm looking for something like the Active Directory schema, only for IIS. I want a list of all the objects I can configure, which objects they can be contained in and wha...