I've been doing a lot of DB refactoring lately and synonyms have come in incredibly useful. When I originally put in the synonyms I was thinking they would be very temporary while I refactor. Now I am thinking there might be some good reasons to keep some of these synonyms around. Has anyone used them as full blow abstraction layer? Wh...
I am trying to build an API that gives data on various cities. Currently, there is a table called APIPriv, which has the following columns: =================================================== idApiPriv | idClient | Daily | Hourly | DevKey =================================================== 1 | 23 | 'Y' | 'N' |[M...
I need an elegant way to store dynamic arrays (basically spreadsheets without all the functionality) of various sizes (both x and y), mostly being used as ENUMs, lists, lookup data, price sheets, that sort of thing. Multi-lingual would be a great bonus. Speed of the essence. Here's an example of a typical "sheet" ; | 1 | ...
I'm offering a search option for my users. They can search on city name. The problem is that my city names I have stored are things like "Saint Louis". But I want to find Saint Louis even if the user types in "St. Louis" or "St Louis". Any suggestions on how I could create a lookup table to take that into account somehow? ...
A Report can have multiple charts. The Chart table looks as follows: Chart -- Id, ChartId, ReportId, ... The ChartId above can map to the ChartId to either one of the following Chart Types: Line: ChartId, Thickness, YAxis, XAxis, Color, ... Pie: ChartId, Radius, Color, ... Bar: ChartId, Width, Color, Border, ... I am using SQL Se...
I am currently redesigning a legacy database to run on SQL Server 2005, and I want to replace most of the old float-columns with decimals. Decimal(15,4) would be sufficent for my needs but the SQL Server doc states that this would use the same storage space (9 bytes) as a Decimal(19,4). Having a larger number in the same storage space s...
...what's next? After you defined what actors do what actions, which way do you go? Do you model the database or do you prefer to start with the classes? I thought the better approach was to start with a class-like modelling diagram, to focus on relationships between objects. This has proven to be wrong because I went too deep in detai...
I often come across web applications that expose internal database primary keys through forms like select boxes. And occasionally I see javascript matching against an int or guid magic value that switches the logic. Is is a best practice to avoid leaking all internal identifiers of rows in your web application to prevent outsiders from...
I'm building an intranet web app with an Oracle back end. The Oracle DB will be replicated on another server for reasons that aren't important to this discussion. I'm reasonably certain we'll be using Oracle Basic Replication, not Advanced. Can someone explain to me why most discussions around Primary Keys go something like: Use of ...
Is there any advantage of using int vs varbinary for storing bit masks in terms of performance or flexibility. For my purposes, I will always be doing reads on these bit masks (no writes or updates). ...
I have a three tier system, SQL Server backend, hand written data access layer, and using stored procedures. I have a table called EventTable. Each row is an 'Event'. An Event has a primary key, and a start date. CREATE TABLE EventTable ( ID INT IDENTITY(100,1) PRIMARY KEY, StartTime DateTime NOT NULL ) There is a stored proced...
Since Google App Engine doesn't permit joins, does this mean that I have to take all of the tables in my web app and figure out a way of combining them into a single huge table? ...
I am not very familiar with databases and what they offer outside of the CRUD operations. My research has led me to triggers. Basically it looks like triggers offer this type of functionality: (from Wikipedia) There are typically three triggering events that cause triggers to 'fire': INSERT event (as a new record is being inserted...
Anyone now what is a draft design of a database??? Is it the design process and steps like: Determine the purpose of your database Find and organize the information required Divide the information into tables Turn information items into columns Specify primary keys Set up the table relationships Refine your design Apply ...
Likewise are there design patterns that should be avoided? ...
I have an application where the database back-end has around 15 lookup tables. For instance there is a table for Counties like this: CountyID(PK) County 49001 Beaver 49005 Cache 49007 Carbon 49009 Daggett 49011 Davis 49015 Emery 49029 Morgan 49031 Piute 49033 Rich 49035 Salt Lake 49037 San ...
I'm working on the project with relatively big DB table(700K rows). Mistake, I've made while designing DB schema. When rows were increasing, I had to increase ID's column type bigint(x). Now it is bigint(44). I afraid of setting high x value because I thought that it can significantly slows down performance. Maybe I'm wrong.. Please h...
I'm working on a MVC based web app on LAMP that needs some records to be modified only under the approval of a "superior" user. (The normal user can submit changes but they get applied only after this approval) There are is only a table in which this would have to take place, say "events": EVENTS - id - name VARCHAR - start_date DAT...
I am working on a new application that uses a jet (MS Access) database. I have built the database using FluentNHibernate's AutoMapping feature from my C# objects. I need to present this database for an on-line WebEx design review on Tuesday and am looking for a tool to create ERD's for my database that I can group onto slides. The dia...
After gathering info bit here and there and looking at this URL: Here is the proposed design 1. ChartAccounts ChartAccountID INT TenantID INT ChartAccountCode VARCHAR(256) Dimension1Code VARCHAR(16) Dimension2Code VARCHAR(16) Dimension3Code VARCHAR(16) Dimension4Code VARCHAR(16) Dimension5Code VARCHAR(16) Dimension6Code VARCHAR(16) Di...