First off, let me preface this question by stating that I'm really a pretty terrible data modeler. I know only enough to be dangerous. The table I'm building has four foreign keys, two of which reference the same table. Here's the create statement for that table. CREATE TABLE IF NOT EXISTS `abnr`.`reputation_event_log` ( `id` INT ...
I have a legacy application that I am making updates on. A couple of the tables in the database hold monthly information. The tables basically have one row per year and 12 fields for each month. I was wondering whether or not this was the best way to store monthly data. Is it better to have a record for each month? Although I suppos...
For example, I have a "users" table which has an enum column "type" with two possible values: "individual" and "organization." They are mutually exclusive and mandatory (each row must have exactly one value from the possible two. Would this be a good case to use enums? Why so/not? What are some pros and cons on using ENUM (set) types fo...
Hi, I noticed a number of cases where an application or database stored collections of files/blobs using a has to determine the path and filename. I believe the intended outcome is a situation where the path never gets too deep, or the folders ever get too full - too many files (or folders) in a folder making for slower access. EDIT: E...
What are the pros/cons for including a date field as a part of a primary key? ...
I can read the mysql docs and they are pretty clear. But, how does one decide which character set to use? On what stuff does collation have an effect? I'm asking for an explanation of the two and how to choose them... Thanks! ...
In a one-to-many relationship what's the best way to handle data so it's flexible enough for the user to save the slave data before he saves the master table data. reserving the row ID of the master so the I can save de slave data with the reserved master id save slave data in a temporary table so that when we save the master data we c...
I'm involved on a project to make a survey system. We've been hammering out the logic for a few question types, and I could use a second opinion on what is the best way to proceed. We work on a ASP.NET 2.0 website using VB(VS2005) with an Oracle database. In our oracle server, we plan for some tables to organize our data. There's a t...
This is something that made me doubt for a while so I thought it would be a good idea to post it here to find some insight, it's a relational database modeling problem/doubt I have the following problem: I have "questions" that must be in an specific "state", and all changes of state must be audited. I've found two solutions to this, ...
I have a contact database where I can send an email to a single contact or to a group of contacts based on a query - however I would like to add a checkbox function so if I decide I want to send an email only to a handful of people in one of my query lists, I can check their name and it will send. The issue I'm having is I don't underst...
I normally in my projects use such these code: If user.IsInRole("Admin") Then deleteButton.Visible = True else deleteButton.Visible = False But I want to control roles, which can see this button in database. For this purpose how should database design be? Thanks. ...
I'm working on a web app (it is in asp.net mvc framework beta in visual studio 2008) and want to version control it. How do I version control the database files (*.mdf, binary) in the App_Data folder. Is there a way to just store the tables-and-whatever definition of the database or do I really need to version control it's contents? ...
In my database schema I have an entity that is identified. The identifier can be reused and thus there is a one-to-many relation with the entity. Example: A person can have a nickname. Nicknames are not unique and can be shared amongst many people. So the schema might look like: PERSON id name nickname_id NICKNAME id name The issue i...
For example I have 2 tables, Users and UserRelations, and it is a one to many relationship. For the UserRelations table, I can have an identity column and make it the primary key, [RelationID] [int] IDENTITY(1,1) NOT NULL, [UserID] [int] NOT NULL, [TargetID] [int] NOT NULL, Or I can design the table like [UserID] [int] NOT NULL, [T...
What are the things that you would consider when defining indexes, clustered and non-clustered, for SQL Server? Are there any anti-patterns that DB newbies should be aware of? Please explain the "Why" or provide references if possible. ...
I'm working on a database that tracks files and dependencies in projects. Briefly, I have two main tables; the PROJECTS table lists project names and other properties, the FILES table lists files. Every file entry points to a project as a foreign key set to CASCADE, so if I delete a project record from the database, all the file records ...
For a web application I'm developing, I need to store a large number of records. Each record will consist of a primary key and a single (short-ish) string value. I expect to have about 100GB storage available and would like to be able to use it all. The records will be inserted, deleted and read frequently and I must use a MySQL databas...
This is my first attempt at answering my own question, since someone may well run into this and so it might be of help. Using Firebird, I want to combine the results of two queries using UNION ALL, then sort the resulting output on a given column. Something like: (select C1, C2, C3 from T1) union all (select C1, C2, C3 from T2) order b...
It seems to me there are other functional dependencies you may wish to declare (other than superkeys of course), but SQL provides no easy way to do so. Why is this? ...
For a number of years I've used Object Role Modeling to design and validate my database schema designs. (The appurtenant software products have been variously named Visiomodeler, Infomodeler, Visual Studio Enterprise Architect ORM Studio, etc.) It's the only truly conceptual database modeling tool I've ever found to be at all useful; in...