As part of our DB change control process one of the tasks is to reverse engineer the changes into our schema diagram, using Visio. This is a little tedious and we were looking at a way to automate this. We already have a CI and Release to test process that generates the latest DB all we need is some application that will analyse the da...
Just a question about best-practices when upgrading an existing database. Assuming there will be all kinds of modifications to the data itself, the structure, the relations, additional columns, disappearing columns and whatever more. My problem is a simple one. I'm working on a project that will use SQL Server. No problem there, since I...
I'm thinking about table layout for a Hibernate-managed class hierarchy, and certainly the table per subclass technique strikes me as the most appropriate in a general sense. However, thinking through the logic I have some concerns about its performance especially as the number of subclasses scale. To give a very brief (and classic) ex...
Hi, What is the parent and what is the child in a sql relationship? In my case, I have a part (Say screw), and product material. For argument's sake, a product material (eg steel) can only belong to one part (but not in the real world). So this is 1:n. The part will have its pk as a fk in the ProductMaterial table. Which is parent and...
I wanted to know how important it is to use InnoDB table and transactions for an auction website. I know that when there is payment involved the transactions are important, but in this case, there are only bids placed. The bids that are placed however are placed very quickly, maybe a few every second. I was wondering if i couldn't ju...
I have the following tables: Cateogories CategoryID (int) Primary Key CategoryName (varchar) Items ItemID (int) Primary Key CategoryID (int) ItemName (varchar) There is a foreign key constraint on Items.CategoryID. There is a chance that when a new item is created that there will be no category assigned. Is it better to set It...
I am designing a database from scratch from manufacturing products. The key design is as follows: (Currently I am working on MS Access 2007 but my final implementation is SQL Server i.e. SQL Server 2008 being the latest version. Also I would be using only Express edition at this movement. The application will involve a .NET storefront in...
Hi All, I am currently in a debate with a coworker about the best practices concerning the database design of a PHP web application we're creating. The application is designed for businesses, and each company that signs up will have multiple users using the application. My design methodology is to create a new database for every compa...
What are good references for database design diagrams? Examples would be nice. ...
I need a way to represent existential relations in a database. For instance I have a bio-historical table (i.e. a family tree) that stores a parent id and a child id which are foreign keys to a people table. This table is used to describe arbitrary family relationships. Thus I’d like to be able to say that X and Y are siblings without ha...
I am good with ideas, but due to my limited programming experience I sometimes strain to come up with the most effective solution for a given concept. Currently my mind is trying to fathom the most efficient way to reference a database table specific to a data type. Concept I am building an admin interface that allows a site owner to '...
Hello! I want to make a relational database system for a local computer hardware non-profit. There are two inter-related features that I want to figure out how to implement. First, we need to manage our inventory of parts, and second, we need to track donations of computer systems as they come into us, and also as we grant them out. Fir...
Hi, I've always wondered what are the pros and the cons of these ID naming styles in SQL: CREATE TABLE cache ( id INT AUTO_INCREMENT, PRIMARY KEY(id) ); CREATE TABLE cache ( cid INT AUTO_INCREMENT, PRIMARY KEY(id) ); CREATE TABLE cache ( cache_id INT AUTO_INCREMENT, PRIMARY KEY(id) ); Why some developers use "id" in each table, som...
Following on from my previous question, I'm looking to run some performance tests on various potential schema representations of an object model. However, the catch is that while the model is conceptually complete, it's not actually finalised yet - and so the exact number of tables, and numbers/types of attributes in each table aren't d...
I wonder if it's a problem, if a table or column name contains upper case letters. Something lets me believe databases have less trouble when everything is kept lower case. Is that true? Which databases don't like any upper case symbol in table and column names? I need to know, because my framework auto-generates the relational model fr...
Hello, I am creating an little hobby database driven browser based game and I stumbled across this problem: I store money owned by users as an 32bit integer field (to be precise: two fields. One stores money in players hand, the other - money stored in bank). We all know, that maximum value, which can be stored in 32 bits is 2^32-1. I ...
I'm trying to figure out what to call the column in my database table that holds an INT to specific "record version". I'm currently using "RecordOrder", but I don't like that, because people think higher=newer, but the way I'm using it, lower=newer (with "1" being the current record, "2" being the second most current, "3" older still, an...
I would like to see some examples of simple flat file databases and how they are accessed through a data layer. I've written to and read from a flat file before, but I have not ever created a data layer that accessed the data for an application using text files. If possible, it would be nice to see a tutorial that had a data layer that ...
Our application has a fairly large, sprawling DB design, with 175 tables. Most tables are used by one or two primary features, and I'd like to create several little "mini-schemas" to describe the structure of different parts of the system to new hires (without showing them the gigantic total schema which is, frankly, so big as to be use...
I have 2 fields and I need these to be a floating decimal point. I wonder what data type to use while I'm designing bottom-up Linq-to-SQL classes. EG: one value is 1.427600, another value is 1765.030, both would be in the same property/column. ...