My DBA just lost some development work that he did on our development database. Poor fella. So naturally our manager asked him, at our status meeting, how this could happen and how we could avoid this happening in the future. "Source control could alleviate the problem" I suggested... The dba's response; "No, we just backup the server mo...
I am working on a database that has an existing Individuals table that every user type derives from. In other words, there are tables like: Individual: FirstName, LastName, Email, <lots more> Employee: IndividualId Customer: IndividualId etc. Now, I want to add new type of user (WeirdPerson) that does not derive from Individual....
Hello folks, I'm trying to build a searchable database of acronyms and their definitions that are specific to a certain industry. It has been years since I've done any real programming, so I'm a little behind the learning curve. I'm writing the code in PHP, and I'm using MySQL as the database. If this can be done easier in Postgres, ...
ORIGINAL (see UPDATED QUESTION below) I am designing a new laboratory database that tests a wide variety of tests on a wide variety of sample types. The following list is my current candidate for the list of main entities to best model the laboratory work. For each entity, a 1-to-many relationship exists from that entity to the entit...
Given that users must be able to define their own fields on an object, and define allowed values for these fields - on the fly (no compile) - how would you solve this without using EAV? Example: All objects need one or more reference to the 123 statusnumber (1.a or 2.b or 3.c allowed). ...
Given a data stream of continuously arriving items containing a timestamp and text (e.g. a search engine's query log), how would you store the data so that you could efficiently retrieve totals over time to plot trend lines per term? A row-oriented database with tuples like (term, date, count) would work but would not scale with a large...
is there a standard or best practice with database table names and fields in terms of casing (upper, lower, camelcase, etc. . ) ? ...
Hi, I have database application, I want to allow the user to restore the deleted records from the database, like in windows we have Recycle bin for files I want to do the same thing but for database records, Assume that I have a lot of related tables that have a lot of fields. Edit: let's say that I have the following structures: Repo...
Hello there When making relations between tables (in mysql), I have encountered a naming dilemma. For example, if I was creating a site where a project could be created by multiple users and also read by multiple users, to link a questions and user tables, I would potentially need two tables. **project_authors** questionId userId a...
Does a Foreign Key referencing a Primary Key need the NOT NULL constraint in a PostgreSQL database? The database is highly normalized and will be very large. I do not wish to add extra constraints that will slow down the queries even more if said queries are unneeded. ...
In a multilanguage application with lookup tables, what's the best way to handle translations? For example for a country lookup table, a US user should see English country names, as a German should see German names. But still their IDs should be the same. I can think of the following: Add a different lookup table for every language U...
I have a new layout to figure it has 3 tables UnfiledFilesSortedFiles FileVersion The process will start with an UnfiledFile Record and a FileVersion and several versions may be added all joined to the same UnfiledFile From there I am going to need to tie all the Versions for a particular UnfiledFile record with a SortedFile Reco...
In our dev group we have a raging debate regarding the naming convention for Primary and Foreign Keys. There's basically two schools of thought in our group: 1) Primary Table (Employee) Primary Key is called ID Foreign table (Event) Foreign key is called EmployeeID 2) Primary Table (Employee) Primary Key is called EmployeeID Forei...
The reason I want to use a Guid is because in the event that I have to split the database into I won't have primary keys that overlap on both databases. So if I use a Guid there won't be any overlapping. I also want to use the GUID in the url also, so the Guid will need to be Indexed. I will be using ASP.NET C# as my web server. ...
I wish to persist some extra data in a many-to-many relationship by having some extra fields in the association table. For instance, I would like to keep track of what role a user has in a network, such as 'member', 'moderator', 'admin' etc. I would also like to keep track of when he/she joined the network. Now, what I am looking for is ...
We have a SQL server with many databases in it. We have customers with multiple versions of a similar app and multiple apps for a single customer. Almost all databases are tied to specific websites. How do you stay organized with your database names? Surely there is no single answer, but do you have a database naming strategy that is w...
Since I cannot do JOIN queries against the App Engine Datastore, I would like to denormalize my entity objects a little to include computed values, in effect creating something like a function-based index. @Persistent Employee manager; @Persistent // de-normalized stored join value Integer managerDepartmentCode; This works fine,...
I need to explain this by example: Is there a best practice or preference for specifying a DateTime and BIT in a database table? In my database I have a Widget table. I need to know if a widget is "Closed" and it's "Closed Date" Business rules say that if a widget is closed, it must have a closed date. If a widget is not closed, i...
I have a 'customers' table. Each record contains a customer name, email, address, etc. I have an 'skus' table. Each record contains the name of an SKU. e.g. "Super Software Bundle". An SKU represents a purchaseable item at a single price point. The purchased item can include one or more products. e.g. The SKU record named "Super Softw...
When taking a database from a relatively un-normalized form and normalizing it, what, if any, changes in resource utilization might one expect? For example, normalization often means more tables get created from fewer which means the database now has a higher number of tables, but many of them are quite small, allowing the often used on...