database-design

Database Design Problem

I'm trying to design a database for a server and database inventory and I'm looking for a good database design. We have tables for server clusters, stand alone servers and databases. I would like to represent the following relationships in the database: A one to many relationship from cluster to servers. A one to many relationship fro...

Best approach for a table with lots of responsibilties

I'm currently working on creating electronic version of various request forms. Each form will more than likely require some specific data about the user. For sake of argument, here's a simplified version. Form Required Info ¯¯¯¯¯¯¯¯¯¯¯¯¯¯ ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯ ...

Using Primary Key / ID Field as an identifier in a URL

What are the pros and cons of using your databases primary key as a URL identifier? As an example, http://localhost/post/view/13 - 13 being my primary key for my posts table. Some sites like reddit use what I assume is a unique id that is not the primary key but still unique to help identify the link: http://www.reddit.com/r/funny/com...

Organization Id Field as a Composite Primary Key

We currently have a system where each client gets their own database when they sign-up. Not surprisingly this is getting out of hand. We are preparing merge all these databases together to a single db. In order to do this we need to note which organization the row belongs. How best is this handled on the database and what pros/cons (...

How to design DB for question-answer (MySql)

I need to design a DB for a forum. I am separating the root post from it's sub-posts for various reasons. I need the text the user enters to be optimally search-able, also from performance point of view. My question, should I separate each table (root-posts and sub-posts) into two tables: root-posts_meta (hold data such as id,creatio...

First Name Variations in a Database

I am trying to determine what the best way is to find variations of a first name in a database. For example, I search for Bill Smith. I would like it return "Bill Smith", obviously, but I would also like it to return "William Smith", or "Billy Smith", or even "Willy Smith". My initial thought was to build a first name hierarchy, but I...

Database design issue - multiple scores in different categories

Say I have a database of clothing products. In addition to many other fields, the database needs to store ratings (marks out of 10) on different categories for each product. For example, one category could be 'occasions'. The occasions could be as follows: Wear to the office Wear to a wedding Wear on a date ... Wear to a funeral Each...

Why LDAP is not popular ?

We start to use LDAP and I wonder why LDAP is not that popular? I read that it has faster reads than database, however slower writes. EDIT: Its the first time i use LDAP and i have been programming in enterprise for sometime now. That is the reason why i thought its not popular, plus i never heard about it at school. ...

What does "Data Massage" mean?

I am doing some reading, and came across avoiding an internalStore if my application does not need to massage the data before being sent to SQL. What is a data massage? ...

Software Architecture and Database Design: One database/web application per each company or one database/web application for all companies?

Hello gurus, I'm designing a web driven SQL database application from ground up. The application will manage information for clients that are in the same type of industry. In other words, the information (entities and relations among them) regarding each client does not vary too much from one to the next. However, the volume of the i...

What are the most important considerations when designing a database?

I would like to know from the experienced programmers what they consider to be the most important considerations when designing a new database. ...

Structuring a database where products have multiple packaging types from multiple vendors

Let's say I have a Products table, and there is a "default" unit the product is sold in (EA let's say, for individually). The product is also available from some vendors as BX (box) and CT( carton), each containing a certain amount of the "default" unit (in our case let's say a box is 6 and a carton is 12). The product has a regular ...

E2L: How do you handle Foreign Keys that participate in multiple relationships?

I have a Database model like this FlowObject FlowObjectID (PK) Description Active ProcessObject FlowObjectID (PK, FK) HasSubmit DecisionObject FlowObjectID (PK, FK) YesFlowObjectID (FK) NoFlowObjectID (FK) YesCaption NoCaption When I try and use create my Entity model I get this warning in my project. Foreign Key constraint 'FK_Pro...

Database design for data acquisition with multiple variable types

I'll design and implement database for data acquisition and started to wonder what might be the best relational database structure for multivariable solution. There can be dozens of variables (configurable), different types (boolean, integer, float at least, maybe string). Values of different variables are not related. I need to store va...

How can I implement a semantic ontology in Ruby on Rails?

I'm working on a "twitter filter" - more to learn ruby on rails than anything else. The idea is that I use a semantic ontology to lookup a users interests. So if a user says they're interested in "sports" that means flag any tweets that discuss "sports" "golf" "football" and so on. I'd like to be able to expand it to any hierachial of t...

Propagated delete in code or database?

I'm working on an iPhone application with a few data relationships (Author -> Books for example). When a user deletes an Author object from the application, I have a few SQLite triggers that run on the delete to remove any books from the database that have a foreign key matching the Author's primary key. I'm also using a trigger to i...

Is it a good idea to have Multiple FKs in one field in MySQL?

I'm setting up a database and I'm interested in having a facebook-like friends system. My original plan was to have a table like so: uid friends 4 30,23,12,33 30 54,92,108 with all these numbers being FK's to tables with user information. I was told that this is inadvisable and practically impossible since MySQL will only deal w...

varchar Fields - Is a Power of Two More Efficient?

Is it more efficient to use a varchar field sized as a power of two vs. another number? I'm thinking no, because for SQL Server the default is 50. However, I've heard (but never confirmed) that sizing fields as a power of 2 is more efficient because they equate to even bytes, and computers process in bits & bytes. So, does a field declar...

What tag schema(s) are the most efficient/effective?

http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html Stackoverflow's tag handling is among the best that I've seen so far. Does anyone know if it is a schema pattern I could get some ideas from? Otherwise, I'm just looking for suggestions on what tag schemas others have successfully implemented. ...

Backup MySQL users

How do I backup MySQL users and their privileges? Anything like mysqldump? I am looking for something like mysqldump -d -u root -p MyTable > Schema.sql ...