Lets say you build an application which needs to serve several customers (data is never shared between customers) and the customer data itself is sensitive. How would you design the database to prevent that suddenly (e.g. a bug) data from one customer is visible to another? E.g. We have a projects table which contains projects of a cus...
Taking the typical products / categories many-to-many relationship you'd typically have a relationship like follows: table -- ProductsCategories column - ProductId column - CategoryId Is it ill advised to add other properties to that relationship, things that only exist on that specific relationship. Such as (please forgive my terribl...
I have a text field that can be 30 characters in length, however it can achieve around 2.000 characters. For "future safety", it would be nice if this new column could support until 3.000 characters. I think the average size of this field is 250 characters. Which SQL Server 2000 data type is better to increase performance and disk spac...
What are common database development mistakes made by application developers? ...
Hi, I'm learning PHP and MySQL and I'm creating a movies website for learning purposes. I want to insert variables and arrays containing movie info into a database but in different tables. This is what I have right now: include('inc/connection.php'); $conn = dbConnect(); // create SQL $sql = 'INSERT INTO movies (movieid, title, year,...
Hi Stack Overflowers: I'm building a Ruby on Rails application that has several different models (e.g. Movie, Song, Photo) that I am storing movie clips, mp3s and photos. I'd like for users to be able to comment on any of those Models and have control over which comments are published. Is the best practice to create a Comment model with...
I've been searching around trying to find an answer both here and google, although I've found some pointers I haven't quite found a solution. If you have a simple RSS reader with a database, you might have a couple of tables for storing feeds (ignoring dealing with subscribers here): Feeds (feed-id, feed-title, feed-url) Items (item-i...
Hi! I have to store on a database four kinds of object on a database. These objects have the same atributes: Name (varchar2). Description (varchar2). File (Binary blob). Maybe I can use a table to store these objects adding a column to identify the kind of object but I need to store a lot of objects (> 1,000,000 or much more). My q...
I need to visually understand the database schema of an open source erp software (Dolibarr), for that have found a java based tool dbschemaviewer which make use of eclipse and GEF to map out an existing database. I let it do the process overnight but no results I need some other software that can map existing database schema visually an...
How do you handle real name conflicts? Is there an established best practice or UI design pattern for disambiguating records like this? If authors can have many articles but more than one author can possibly have the same name how would you enable users to select the author they actually want when creating articles? I can't dictate th...
I have a database with a report table. This table represents report data, and has constraints. Now the application is used by firemen, and they might have to run off and put out some fires. So in the middle of filling out data for this table, they might have to run off. What we do is when the session run out we save the data to a draft t...
I am designing a product database and I have a design question. There are several different categories of products, say books, video games, home electronics, and pet supplies. There are some things in common, say the manufacturer, and price, while other things are unique to each category, say power consumption. The individual produc...
For the given relation, determine the current normal form, and list the reason why it is in that normal form. Then, using ONLY the information given, transform the relation into third normal form. Note that by default all attributes are depended on the composite primary key. Order( CID, VesselID, VoyID, VoyDest, EID, ContID, CDESC...
What are everyone's opinions on normalizing a database out across multiple databases. For instance, say you had a database that allowed different clients to sell products. You had abstracted the concept of an order, order item, etc., but each client wants a different field added here or there. You could add the fields to the specifi...
I'm working on a table design that could involve many NULL values in about 10 fields maybe 75% of the time the fields would be unused. I just generated some fake data (a million records) and could not sense any impact on SQL Server 2005. Size difference was in the KB. Performance - no measurable difference after adding an index to the 3...
We have an application that has 1000+ databases and 600+ sprocs. Each database represents a different client. Problem: We need to move this to a single database while creating as little effect on the ui as possible, meaning dont change all the sproc signatures at 1 time. The connection string currently sets the database attribute, a ...
I am setting up a table where I need the year and month. In MySQL I believe I have 2 options: (1) 2 fields: 1 for year, 1 for month or (2) a date field (the day would always be 1). The 2 fields has the advantage of being some what faster (I think) because MySQL doesn't have to convert the value from a date to an integer although this is...
I've crossed this problem in, oh, every application I've ever written. I'd like a final consensus answer! Is this the most normalized/efficient/proper way to store contact methods or any other data along this pattern in a database? Contact { PK ContactId, ... } ContactContactMethod {PK FK ContactId, PK FK ContactMethodId, Key, Commen...
Hi, I'm building a movies website... I need to display info about each movie, including genres, actors, and a lot of info (IMDB.com like)... I created a 'movies' table including an ID and some basic information. For the genres I created a 'genres' table including 2 columns: ID and genre. Then I use a 'genres2movies' table with two colum...
Hi, are there any books on designing and building database software (not organizing table layouts), like optimum file formats etc. ...