Hi! I'm building a "small" personal web app in Ruby on Rails. I've set it up so that I'm using a MySQL database. The idea is that I'm gonna store and navigate many kinds of data (notes, bookmarks, movie and appliction ratings) with this app. I want to index/categorise this data in a pseudo-directory structure. (The directory structure ...
What are the best practices for keeping referential integrity across multiple databases? since there's no built-in functions Or is it better to partition a single database? Update See kevin's example below. that is my situation. My inventory database has tables that reference the employeeId in the employees database. These databases a...
In Object Role Modeling (ORM), given an entity of thing that had a relationship to an entity of type and where the type entity can be specified to live and the thing entity could have a value for date of birth, how would I specify a constraint that would exclude instances of thing from having a value for date of birth if the instance of ...
I have a 'SessionVisit' table which collects data about user visits. The script for this table is below. There may be 25,000 rows added a day. The table CREATE statement is below. My database knowledge is definitely not up to scratch as far as understanding the implications of such a schema. Can anyone give me their 2c of advice on som...
Is creating user defined types instead of using existing types best practice? In my previews work place all basic types were predefined, is it best practice? what advantages and what disadvantage it has.Thanks a lot! ...
I'm looking for a database visualization tool for OS X. MySQL Workbench looks promising, but the binaries provided are Intel only and I haven't been able to get the source to compile on PowerPC yet. Are there any other database visualizations tools out there for OS X? What other tools do you use for database development/administration? ...
There seems to only be 2nd class support for composite database keys in Java's JPA (via EmbeddedId or IdClass annotations). And when I read up on composite keys, regardless of language, people keep coming across as them being a bad thing. But I cannot understand why. Are composite keys still acceptable to use these days? If not, why not?...
Is there any merit in creating a policy that requires developers to specify default values for all null columns? ...
I know that logically, there are some cases where NULL values make sense in a DB schema, for example if some values plain haven't been specified. That said, working around DBNull in code tends to be a royal pain. For example, if I'm rendering a view, and I want to see a string, I would expect no value to be a blank string, not "Null", an...
Hi, this is a simple question. With PHP and MySQL, I'm trying to build a bug reporting application. Currently, the user can post bugs and a list of bugs is portrayed to the technician/administrator. How do I make it so that many technician or administrators can reply to the report (thread?) As in both mysql table layout and PHP codi...
I have got a normalized transactional database. I am setting up a data warehouse for reporting purposes. Are there any tools which will automatically make de-normalization suggestions? Or something to automate database changes in that manner? ...
Hi, I am working on a winform (.NET) application which includes Orders, Invoices, Service Orders, Ticketing etc. It it necessary for these enities to be sequential when numbering their IDs? IMO no. Take an order for instance, it can only be valid once it passes thorugh the business layer, during that proocess another order could've been...
what will be the effect if uniqueidentifier data type column is clustered/Non Clustered index in a table sql server 2005/2008. I read it is badly designed table, how to avoidy this problem and what is the best solution? ...
I am designing a dashboard to display corporate metrics (currently with ASP.net 3.5 in C#), and looking for advice on design. I'm not as used to OOP in web applications. There may be many different metrics that can be seen by different users in a Many-to-Many relationship. I am storing the users in a local SQL database and it of cours...
I've lately seen a database where there was a table Types with columns Id, Key and Name. Id was just an Id of the type, Key was a short key name for the type, for example "beer", and the Name was text that could be displayed for the user (for example, "Our greatest beers"). Id was of course unique and was a primary key for this table....
I'm writing a second interface to a database application to get around some shortcomings of the original interface. Unfortunately, if I create new records, expected audit trail records will not be generated. It sure would have been nice if the database design had worked such details into table triggers, or else provided a stored proced...
I'm creating a gallery. I want users to be able to choose who can view their photos by group or individual. I figure I should have a table can_see with two columns (user_id, photo_id). This should work for individuals, but it won't work if I toss group_ids in there too. I'm thinking the best approach might be just to add each individual ...
I'm making a gallery, somewhat similar to Facebook's. I want users to be able to tag other users in photos, including non-registered users. Is it best to have a table in_photo with columns (user_id, name, photo_id) where user_id is null if the user is unregistered, and name is set, and vice-versa, or should I scrap the name column and cr...
I've seen several questions posted here about recommendations for UML diagramming tools as an alternative to Visio. What I seem to be missing, though, is a good database diagramming tool for OS X. I have done all of my development work in the .NET world for the last few years, and for better or worse Visio has been my go-to tool for data...
Hi, My scenario is I have a table with columnxs A, B, C and D. A is the PK and B and C are both foreign keys. I intend to add an exta column to this table E, the side effect of this within the application is it will cause a row to be inserted after which there will be duplicate values within columns B and C. Does this break any refere...