If I create a table with datetime default getdate() field that is intended to keep date&time of record insertion, which name is better to use for that field ? I like to use Created and I've seen people use DateCreated or CreateDate. Other possible candidates that I can think of are: CreatedDate, CreateTime, TimeCreated, CreateDateTime,...
My company has a handful of apps that we deploy in the websites we build. Recently a very old app needed to be included along side a newer app and there was a conflict w/ a duplicate table name needed to be used by both apps. We are now in the process of updating an old app and there will be some DB updates. I'm curious what people ...
Any suggestions on whether fewer check constraints are better, or more? How should they be grouped if at all? Suppose I have 3 columns which are VARCHAR2(1 BYTE), each of which is a 'T'/'F' flag. I want to add a check constraint to each column specifying that only characters IN ('T', 'F') are allowed. Should I have 3 separate check c...
Users need to be able to add a specific type of column to an otherwise static table on my web page and then save the information they enter in there to the database. I've been told that in reality they will almost never go over 5 columns but I would rather support N. The columns will all be of the same datatype. My first thought was t...
I have a database with two tables - let's call them Foo and Bar. Each foo may be related to any number of bars, and each bar may be related to any number of foos. I want to be able to retrieve, with one query, the foos that are associated with a certain bar, and the bars that are associated with a certain foo. My question is, what is th...
Suppose I have a system where I have metadata such as: table: ====== key name address ... Then suppose I have a user-defined type described as so: datasource datasource-key A) are there systems where it's possible to have keys based on user-defined types? B) if so, how do you decompose the keys into a form sui...
We have quite a large database schema and our business analysts would print it and take it to meetings. Is there any way to get schema spy to print an ERD to 1 A3 page? ...
So i'm trying to figure out the smartest way to do this. I have a list of businesses that can have photos and videos. i've created tables for: business, photos, videos. I also created a table for specials. I want to have featured businesses on the front page. Should I create a table called featured and have the businesses id stored in th...
I have a table of values, with one of the columns being of type SET. If it currently has the members ('a', 'b', 'c', 'd'), how do I add 'e' to the possible values? I realize that using a SET type is a little strange, and I'm unclear why one would use it instead of a foreign key to another table with the values of the set, but I didn't ...
As a learning exercise I'm trying to put myself a blogging system. The goal is to code something that will let me create multiple blogs, like blogger.com or wordpress.com, but much simplified. I would like to ask you, what do you think is best database design for this type of script. Is it better to have one big table, containing po...
Hi I am looking for ideas to populate a fact table in a data mart. Lets say i have the following dimensions Physician Patient date geo_location patient_demography test I have used two ETL tools to populate the dimension tables- Pentaho and Oracle Warehouse Builder. The date, patient demography and geo locations do not pul...
How can I manage multiple versions of a database easily? I have some data (as views as selects for data originating in tables from other schemas), which other database may reference using various means including database synonyms & links. I wish to provide a sort of interface/guarantee in-case future for applications/databases which u...
Hi, I'm trying to work out a sensible approach for designing a database where I need to store a wide range of continuously changing information about pets. The categories of data can be broken down into, for example, behaviour, illness etc. Data will be submitted on a regular basis relating to these categories, so i need to find a good w...
Hi, I'm planing to build some database project. One of the tables have a lot of attributes. My question is: What is better, to divide the the class into 2 separate tables or put all of them into one table. below is an example create table User { id, name, surname,... show_name, show_photos, ...) or create table User { id, name, sur...
Problem Description: We have a requirement to store the snapshot of an entity temporarily in the database until a certain period of time until all the processes to approve the data are completed. Once all approvals are completed the data shall be permanantly persisted into the actual table. Example: Consider an entity called "User". O...
I have an app that will have entries of both varchar(max) and varbinary(max) data types. I was considering putting these both in a separate table, together, even if only one of the two will be used at any given time. The question is whether storing them together has any impact on performance. Considering that they are stored in the hea...
Suppose you have two sets of conceptual entities: MarketPriceDataSet which has multiple ForwardPriceEntries PoolPriceForecastDataSet which has multiple PoolPriceForecastEntry Both different child objects have near identical fields: ForwardPriceEntry has StartDate EndDate SimulationItemId ForwardPrice MarketPriceDataSetId (forei...
Note I have completely re-written my original post to better explain the issue I am trying to understand. I have tried to generalise the problem as much as possible. Also, my thanks to the original people who responded. Hopefully this post makes things a little clearer. Context In short, I am struggling to understand the best way to...
Hello guys! I hope there's someone who can help me suggest a suitable data model to be implemented using nosql database Apache Cassandra. More of than I need it to work under high loads and large amounts of data. Simplified I have 3 types of objects: Product Tag ProductTag Product: key - string key name - string .... - some other...
I am creating a web site for a customer and they want to be able to create articles. My idea is to tag them so I am going to implement the system. What is the best design, both from an architectural and a perfomance perspective: 1. To have table with all tags and then have a one to many relationship table that links a tag like this: ...