I'm quite new to database design and have some questions about best practices and would really like to learn. I am designing a database schema, I have a good idea of the requirements and now its a matter of getting it into black and white. In this pseudo-database-layout, I have a table of customers, table of orders and table of products...
Looking for a tool that will automatically create a graphical representation of a MySQL DB schema from a live database, showing tables, columns, and relationships (an ER diagram). Ideally something that would run on OS X would be great but I'll take what I can get and run it on a different machine if necessary. I checked Google and del....
I've been reading a lot lately about how joins in DB queries slow things down. Evidently Google App Engine doesn't even allow them. I'm wondering how people design an app with no joins though. For example I'm working on an app that has contacts and organizations. A contact can be in many organizations and an organization can have many c...
This is an XML schema question. I know that xsd:all element can't appear in a sequence (must be the top level element of its type). That is, I cannot use the following: <xsd:complexType name="Application"> <xsd:sequence> <xsd:element ref="Name"></xsd:element> <xsd:all> <xsd:element ref="ADD"></xsd:element> <xsd:...
What's the best way to store a date value for which in many cases only the year may be known? MySQL allows zeros in date parts unless the NO_ZEROES_IN_DATE sql mode is enabled, which isn't by default. Is there any reason not to use a date field where if the month and day may be zero, or to split it up to 3 different fields for year, mon...
Greetings stackers, I'm trying to come up with the best database schema for an application that lets users create surveys and present them to the public. There are a bunch of "standard" demographic fields that most surveys (but not all) will include, like First Name, Last Name, etc. And of course users can create an unlimited number of ...
I am working on a small widget for BlogEngine.Net. My widget is going to take a person's shared items atom feed and print the title, website url, date, and atom url. To complete this task, I have begun to use Linq and XML. Here is the problem. The atom feed Google Reader uses is located in the source element, which has an attribute ...
Are there any good tools for visualising a pre-existing database schema? I'm using MySQL if it matters. I'm currently using MySQL Workbench to process an SQL create script dump, but it's clunky, slow and a manual process to drag all the tables about (which would be okay if it wasn't so slow). ...
I want to store certain items in the database with variable amount of properties. For example: An item can have 'url' and 'pdf' property both others do not en instead have 'image' and 'location' properties. So the problem is an some items can have some properties and others a lot. How would you design this database. How to make it se...
Is there a performance gain or best practice when it comes to using unique, numeric ID fields in a database table compared to using character-based ones? For instance, if I had two tables: athlete id ... 17, name ... Rickey Henderson, teamid ... 28 team teamid ... 28, teamname ... Oakland The athlete table, with thousands of playe...
So recently on a project I'm working on, we've been struggling to keep a solution's code base and the associated database schema in synch (Database = SQL Server 2008). Database changes occur fairly regularly (adding columns, constraints, relationships, etc) and as a result it's not uncommon for people to do a 'Get Latest' from source ...
I know you can get metadata and schema info from relational stores in Microsoft SQL Server 2005+ via: INFORMATION_SCHEMA Object Catalog Views What is the equivalent of this in Microsoft Analysis Services? ...
Hello, I am trying to create a data access layer using t4 and C#. I would like to be able to provide a DAL where I can access any database. The problem I am having is with some of the schema information. I am able to retrieve some information like column and table names but I would also like other information like the name of the primar...
I am making a DAL template with C# and I am wondering what is the most efficient way to access schema information with ODBC and OleDB. I need the columns, column types, and primary key information. thanks ...
We're planning to move a SQL 2000 database to SQL 2005, and I am familiar with the ability in 2005 to create tables or other objects under a variety of owner/schemas. We didn't really have that ability in SQL 2000, so I'm wondering what my guidelines/best practices would be for creating/managing multiple schemas. Should I create one sc...
I like pure relational design but sometimes there is a need for an Entity/Value method of storing data. Especially where the user needs to frequently create a new type of data. I have seen in some commercial software that they dynamically create standard tables rather than use EV tables. Obviously this isn’t a solve-all solution and ca...
I would like to use the ASP.NET Membership Provider security framework but would like it to use SQL Server schema's. So instead of having tables such as aspnet_Membership I would prefer it to be something like sec.Membership. Has anyone tried this and got it to work? ...
Do you prefix each field in a table with abbreviated table name? Example: Table: User Fields: user_id user_name user_password Or do you do you name your fields minimally? Fields: id name password If you have used both, then which format you feel helped you most in the long run? Edit: This question doesn't seem to have a definite...
I am parsing XML with an XMLReader using a XMLReaderSettings object with the event handling setup to carry out schema validation where appropriate. However it seems that the error catching only occurs once per level of XML. This means that any subsequent errors at that level are ignored. Is there any way I can get the Error handling ev...
I have to make a schema for an XML format that's already being used. The existing XML is being generated already by a different program, and it sounds like it would be difficult to track down all the places that would need to be changed in order to use a more regular XML format. Here's an example similar to our XML structure: <data> ...