We bought a third party survey tool - ClassApps SelectSurvey - which is to be shared among at least ten of our company's applications. In our apps we would like to link directly to a given survey rather than having the user have to hit the survey sign on screen, and select from a huge list of surveys. Question is, given that a survey h...
I'm working on a web application that will be a hosted, multi-user solution when it is finished. I'm trying to figure out the best way to handle the database design for my app. Specifically, I need to figure out how to handle multiple, separate accounts. The way I see it, there are a few options: 1) Have one set of database tables. I...
We develop a commercial application. Our customers are asking for custom fields support. For instance, they want to add a field to the Customer form. What are the known design patterns to store the field values and the meta-data about the fields? I see these options for now: Option 1: Add Field1, Field2, Field3, Field4 columns of type...
How do I best design a database where I have one table of players (with primary key player_id) which I want to pair into teams of two so that the database can enforce the constraint that each team consists of exactly two players and each player is in at most one team? I can think of two solutions, but both I'm not too happy about. One ...
Suppose I have two tables in a parent - child relationship. Let's call them "sites" and "buildings" where a "site" is a parent of one or more "buildings". I want the IDs to be unique across databases so I will be using GUIDs for the IDs. Should I prefer generic names for the ID fields in the tables or strongly typed names and why? Exa...
I'm doing a project in the field of multilevel marketing on .Net and SQL server. In the database it should save like a binary tree. How should i design a database? ...
Hello Again my fellow programmers out there, I'm designing and programming from scratch a online shop. It has a Module to manage "Orders" that are recieved via the frontend. I'm needing to have a status to know whats happening with an order in s certain moment, let's say the statuses are: Pending Payment Confirmed - Awaiting shipment...
What is the analogue of a class diagram in the world of relational/SQL databases? And what is a good way to generate this equivalent in Eclipse? Free is better, but good and commercial is fine as well. ...
Is there a good way to implement many-to-many relation between rows in single table? Example: table to store word synonyms: -- list of words CREATE TABLE word ( id integer PRIMARY KEY, word varchar(32) NOT NULL UNIQUE ); INSERT INTO words (id, word) VALUES (1, 'revolve'); INSERT INTO words (id, word) VALUES (2, 'rotat...
Let's says I have a table which has many fields linked to values from other "value tables". Naturally, I declare foreign key constraints on each and evary of them to enforce integrity. What if I finally get the number of such fields in the range of 20-30? Will it somehow "slow" table operations or not really? ADDED: Value tables are ex...
I am designing a database for a web application and would like to have a table for configurable global settings. Is there any established best practice for the design of this table? I can conceive of two possible solutions, and I'm sure there must be others. The simplest would be just storing key/value pairs in there as strings. This w...
Say you've got a database like this: books ----- id name And you wanted to get the total number of books in the database, easiest possible sql: "select count(id) from books" But now you want to get the total number of books last month... Edit: but some of the books have been deleted from the table since last month Well obv...
I am working on an enhancement to a Flex dashboard that will allow the users to save searches. I'm using BlazeDS and Java/Spring/SpringJdbc on the server side. My question is this: how would you model a search that has one or more criteria, such as: Value Date between 2009-10-01 and 2009-10-31 Currency = 'USD' Bank Name starts with ...
I am no DBA. I do have quite a bit of experience in retrieving data out of a DB for reporting purposes, but the actual designing of one I have little to no experience with. So I am hoping someone here can give me some guidance on the best way to achieve what I am trying to do: One part of the database that I need to design is the stor...
I need to create an article system where no articles are ever deleted. Even more, they want all article edits, once the original is saved, to create new articles rather than overwrite the old ones. So, person A writes article 1 and saves it. Person B edits article 1. What is the best way to organize this database? My thoughts are: ...
I am attempting to build a database for inventory control using a large number of tables and enforced relationships, and I just ran into the 32-relationship (index) limit for an Access table (using Access 2007). Just to clarify: the problem isn't that the Employees table has 32 explicit indexes. Rather, the problem is the limitation on...
I have a data set which consists of an ID and a matrix (n x n) of data related to that ID. Both the column names (A,B,C,D) and the Row names (1,2,3) are also important and need to be held for each individual ID, as well as the data (a1,b1,c1,d1,...) for example: ID | A | B | C | D | 1 | a1 | b1 | c1 | d1 | 2 |...
I heard this phrase when I was listening to dot net rocks this morning. apparently linq to sql only supports one to one table mapping. ...
I'm working on a PHP web application with PostgreSQL. All of the SQL queries are being called from the PHP code. I've seen no Views, Functions or Stored Procedures. From what I've learned, it's always better to use these database subroutines since they are stored in the database with the advantages: Encapsulation Abstraction Access rig...
How can I ensure the records in a database can not be altered by other than the middle tier software (e.g. discourage the DBA of changing values)? I want to implement a simple multi-tier accounting program using open-source stack. The primary function of the application is to track money paid for one product. The main part of the data m...