How do I stop ERWin from creating UDT during Forward engineering
How can I stop ERWin from creating and using UDT (user defined data types) during the Forward engineering process? ...
database-design
Database Design: Many ugly columns, or separated into rows
I'm designing a database and I've hit a spot where I'm not sure which is the preferable design, so I decided to get some input here. The problem is that I have several repeating pieces of data for columns, along the lines of page1: aName, aSize, aColor bName, bSize, bColor cName, cSize, cColor page2: aName, aSize, aColor bName, bSi...
How to prevent foreign keys from messing up?
This is how my tables look: tableMembers | memberID as primary key tableAddress | addressID as primary key references memberID tableSubscription | subsID as primary key references memberID This database is for a subscription site. Basically, if a row is inserted into tableMembers, a corresponding row is inserted to each of the other...
How to efficiently store ever changing datasets (search results) for periodic reports
Hi all. I am having trouble coming up with a good way to store a dataset that continually changes. I want to track and periodically report on the contents of specific websites. For example, for a certain website I want to keep track of all the PDF documents that are available. Then I want to report periodically (say, quarterly) on the n...
How to prevent deep recursive queries with entities consisting of entities of the same type? [Cool example inside]
No worries! It looks more complex than it actually is! Just get down to the drinks! TLDR-version: How to efficiently query and update entities having relationships to other entities? Here's an interesting data modeling scenario with two tables that has been puzzling me: Entities { ID, Name, ScalarValue } ComponentEntities { Aggregate...
Accept Multiple Data Types in Stored Procedure
I'll avoid any explanations and just get to the question. Is there a way to accept multiple data types for one parameter of a MySQL stored procedure? I want to be able to accept both int and varchar-typed data for an IN parameter of the procedure. Is this possible? I know this is not good practice (I'd never choose to do it myself) but...
Composite Primary key verses one column based primary key
Dear All, I have gone through link: http://stackoverflow.com/questions/1264248/database-design-composite-key-vs-one-column-primary-key Mine question: For a table, which can never be referenced as Foreign Key to any other table, what are +ve / -ve aspect in term of insertion/updation of having single column based primary key verses m...
Update SQL CE Table Data
I need to create an application where i can update the data once in a while. I'm trying to figure out the best way so I can create the schema...should i create a database with the tables i need and just copy from the modified database i have? but then i lose relationships that should make my life easier inside the application. Or create ...
database design: Matching sql database keys to php constants?
Well this is a simple design question i've wondered about many times and never found a satisfying solution. My example is with php-sql, but this certainly applies to other languages too. I have a small database table containing only very few entries, and that almost never needs updating. eg this usertype table: usertype_id (primary k...
Database design question with message boxes
I have a question and I'm not sure about the best way to design it. Basically, there are users. Each user can have a message box, and each box in turn can have several messages. The problem is, is that there are some special message boxes, including "New," and "Trash." A user is required to have these two boxes. I had some ideas abo...
software or service to collaborate on a data model?
I'm trying to find a tool where a lot of people can work on a data model collaboratively (doesn't have to be at the same time, a checkout mechanism can work too). Embarcadero has an ER application called ER/studio which apparently comes with a repository system that acts like typical version control software. That sounds great except ER/...
GUID: varchar(36) versus uniqueidentifier
I'm working with a legacy database that stores GUID values as a varchar(36) data type: CREATE TABLE T_Rows ( RowID VARCHAR(36) NOT NULL PRIMARY KEY, RowValue INT NOT NULL ) INSERT T_Rows (RowID, RowValue) VALUES (NEWID(), 1) I would assume that storing a GUID as a uniqueidentifier would be preferable since it's o...
How do i test for a dynamic set of elements from my code without hard-coding?
The question will best be understood with an example. I have Hospital One who have 3 main pharmacies called; Central Pharmacy, Ward Pharmacy, and Private Suite. These pharmacies can be added, deleted, and extended at will. While these go on i would love to know from my code when we are talking about a Central Pharmacy or a Ward Pharmac...
SQL Server Management Studio 2008: save database diagrams without applying tables just yet
Can you create a database diagram for a database and save it without creating the actual tables? You know, so the boss can check it out before you apply it? ...
How to/Most Efficient way to send a message to many users?
Ok so I have a site with users. I want a user to be able to send a message to multiple users based on a search query. Eg. John searches for "Florida" and this search returns 1 million users/companies. Whats the best way to let john send a message to all those users/companies returned by the search result? Lets say, Susan was 1 of thos...
Should I have a master emails address table in my database?
I have a database where several tables have a column email for storing email address. Because this is for surveys, a lot of values will be identical, more likely identical over names, addresses, etc. Should I just have a master Emails table, and then an email_id column? That way I'm only storing email strings once, instead of multiple t...
Country-based Super User Access
I'm looking to provide super-user access to entities belonging to a specific country. eg. Swedish SU can only admin Swedish entities, etc... However, I'm new to django (taking over an old system) and I need a lifeline. I'd like to be able to specify a relationship table. I've already added a userprofile and with that I have a new fi...
Database structure for storing historical data
Preface: I was thinking the other day about a new database structure for a new application and realized that we needed a way to store historical data in an efficient way. I was wanting someone else to take a look and see if there are any problems with this structure. I realize that this method of storing data may very well have been in...
Composite primary key with foreign key relationships to multiple tables. SQL Server 2008 Errors
USE [Fk_Test2] GO /****** Object: Table [dbo].[Owners] Script Date: 08/20/2010 16:52:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Owners]( [Owner] [varchar](10) NOT NULL, CONSTRAINT [PK_Owners] PRIMARY KEY CLUSTERED ( [Owner] ASC )WITH (PAD_INDEX = OFF, STATIST...
Sql Server Xml Column Best Practices
Hi All, What are the best practices for working with Sql server Xml columns to ensure quick performance and also ease of reporting? How do you set up the column? do you leave it as untyped? or associate it with a schema? Does associating the xml column with a schema improve query performance? Our use of xml columns is as follows: ...