Tag Info

Hot answers tagged

8

Trying to normalize addresses is generally a bad idea. There isn't a lot of value to normalizing addresses. Both of your designs are inappropriate for the vast majority of systems. There are two things you typically do with addresses: Use them to send mail or packages to that location. Use them to do geospatial analysis on that location. Since you ...


7

Refer the the Concepts Guide - Overview of Views for this sort of question: Overview of Views A view is a logical representation of one or more tables. In essence, a view is a stored query. [...] Characteristics of Views Unlike a table, a view is not allocated storage space, nor does a view contain data. Rather, a view is defined by a ...


7

My question is, do you think we should look into upgrading to SQL Server 2014 Enterprise so that we can partition our time sheet tables? No. Absolutely not. On a pocket change server of 8 cores you would be spending ~$50k and be unlikely to see any benefit. I would suggest trying, in this order: Increase the memory allocation to SQL Server. You ...


6

Let's break this question up into a few parts. Q: I need to insert 1mm rows a day. Is that a lot? Not really. 1mm divided by 24 hours divided by 60 minutes divided by 60 seconds gives you about 12 inserts per second. For a rough frame of perspective, it's not unusual to see 1,000 inserts per second in typical commodity servers with no tuning. Granted, ...


5

With respect to your question about using Vertica during the ETL, it's very rarely necessary (although not unknown) to use a different type of database for the ETL. I would not do that unless you perceive a specific need to do so. The only times I've ever heard of this being done due to interactions with legacy data sources. Although @Kermit works with ...


4

I'm going to assume that you already have the budget to implement some data warehouse solution. Just briefly talking about Vertica; it is a load and read optimized platform, and certainly not designed for OLTP. The piece on staging and processing data would need some more thought. Vertica isn't really designed to have data staged, cleansed, and moved into ...


4

The major differences are related to duplicate entries in both data and metadata, and the existence of NULL values. SQL implementations deviate from the relational model for practical reasons, for the most part. Read up on the topic in SQL and Relational Theory by C.J. Date or (Kudos to Bill Karwin for this title by the same author) read pages 2 and 3 in ...


3

If it's a data warehouse system you're looking at, you may wish to consider InfiniDB. It's an Open Source columnar storage engine designed for DW type loads - it falls into the NewSQL paradigm as defined by Michael Stonebraker. There's also InfoBright, which is a similar offering. Of course, you may not require any special software at all - vanilla MySQL ...


3

There are a variety of factors that would cause IT organizations to be cautious about creating databases and giving business users the level of access to those systems that you are, presumably, asking for when you talk about wanting to "play with" the data. First off, since you're in a company that does trading, that implies that there are dozens of laws ...


3

Redundancy does not mean: Columns that have the same data type Columns (or rows) that have the same data value (if this is coincidental) Columns that are foreign keys that link a child table to its parent (including the same data value as FK and PK in the respective tables) Redundancy is not about columns or rows for that matter. Redundancy is about ...


3

You haven't really given us much info about what this data is going to be used for. I mean, you have said what data is going to be stored, but what are you going to do with it? If your purpose is storing the data then reporting on it, then I think you're looking in the wrong place. A simple MySQL or SQL Database would do just fine and the reporting tools ...


2

The question is: will it boost the performance of select to split the table into five tables each corresponding to one of the five possible values of z? Yes, you could use partitioning here. Partition elimination will kick in. The partitioning will act as a leading index column. Which brings me to the following point: Just add z as the leading ...


2

You can use hstore for use EAV. hstore is available from pgsql 8.4. If you have installed json you can then use function hstore_to_json(hstore) select hstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') this will output: {"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", ...


2

If you are using SQL Logins, then basically, no. You'll want to have sqlcmd use the person's domain login to connect to the SQL Instance (the -E parameter instead of -U and -P). You can use a login trigger to ensure that they are only able to connect via sqlcmd if you want to prevent them from logging in via SQL Server management studio (it isn't a perfect ...


2

circle_share1 does not require much in-depth design thought. circle_share2 requires some actual brain cycles to determine if the primary key will have the desired performance. Rows in circle_share1 will be written to the disk in the exact order they are inserted, thereby making inserts quicker. Rows in circle_share2 may be inserted anywhere in the ...


2

I can speak for Postgres. PostgreSQL is really fast to copy to if you are a super user. Postgresql has a copy to command. Merely save the file as a csv (which can also be opened in excel) and use: COPY table FROM 'fpath' CSV HEADER DELIMITER ';' You can copy anything with COPY (SELECT STATEMENT) TO 'fpath' as well. If you must use excel formats like ...


2

0- CASCADE means to 'replicate' the changes that happens on the 'master' table to the 'details' tables. For simplicity, assume the relation (country, city). Each city[detail] belongs to a country[master], or each city is a child of a country. With cascade, if you delete a country, all cities belong to that country will be deleted. In your case, ...


2

My preference would be something in the middle. Because states/provinces and countries are well established entities that don't change over time you can pull those out into separate tables. However, trying to normalize street and city-level data whilst relying on human input is error-prone at best, and at worst you'll end up with some very poor information ...


2

If all you want to do is keep track of which authors contributed to which papers, then all you need is a simple intersecting entity (see more) like this: Here the columns of the SUBMISSION table are just the two primary key columns from PAPER and AUTHOR. These two columns will jointly form the primary key of the intersecting table and will also each ...


2

The actual costs are difficult to quantify as they are often diffused. Often they appear as slower transactions. For a system used rarely, this may not be significant. For a system used for an hour a day, that is taking twice as long as is required the cost would be about 100 hrs a year per person using the system. Slower transactions may also have ...


1

If you don't have a DBA chances are the server hasn't been configured according to simple but critical best practices. You can't really make a definitive diagnosis until you've done that. Engage and expert to assess what needs getting done. Then either make the changes or have them do it.


1

At 14 million rows in your largest table and 16GB of RAM devoted to SQL Server it sounds like your entire DB almost fits in memory. If that is true the problem is likely to be quadratic (or worse) behaviour in your queries, which can only be truly fixed by rewriting the worst offenders. That said, I would try one or both of: Adding some SSD RAM to the ...


1

As usual in similar cases, I would strongly recommend that you look at Open Source solutions - well before the design phase. EVEN if you are doing something truly radical, one can always learn by standing on the shoulders of giants. I have worked as a student on the OpenEHR project, so this interests me. It's been a while, but I would start by looking here, ...


1

That is a tough one, if only there was a way to find things like that online. http://tinyurl.com/q7zxplh Make a list of the high level objects that need, such as planes, airports, routes, people, roles, etc. Then figure out how they are related, how you identify each object and what attributes they would have.


1

If Users can only be members of one group, you need only store a GroupId in the Users table. If not, the pattern you are looking for is commonly known as a "Join" table, containing only foreign keys and perhaps a generated primary key. If you have primary key UserId (uniquely identifies a user record) for the Users table and GroupId for the Groups table, ...


1

All you need is two tables. 1 for Products and 1 for Attributes. You would then have a one to many relationship from the Products table to the Attributes table. This means that each product can have as many attributes as it needs. Table structure would look something like this. Products Product_Id INT PK Auto Increment ProductCode VARCHAR(255) Attriutes ...


1

How to download only the newer and updated entries, and avoid downloading same entry once again? Use a database synchronization tool like SymmetricDS or Daffodil Replicator. You'd have a central database server, like PostgreSQL, with SymmetricDS running on it. Each client, such as an Android smartphone, runs SQLite and SymmetricDS. The SymmetricDS ...


1

It's fairly common for IT departments to have these kinds of policies. Locked down systems are so much easier to manage, and most users have no real need for admin rights or a sandbox environment. There's a few common ways to get around it: Ask for a 2nd PC that's not connected to the network, so you can develop on it without threatening anything. Ask for ...


1

MS Access Vs MySQL MS Access works only on Windows whereas MySQL works on almost all platforms. MS Access does not support Partitioning feature whereas MySQL has composite, Range partitioning support. MySQL is opensource so no cost involved. Performance in MySQL is way better than MS Access MS Access database is more suitable for desktop use with a small ...


1

As well as PostgreSQL (which I recommend), you could also look at Firebird (another excellent choice). What is your development environment? If you're replacing spreadsheets, another good option might be Oracle's APEX with Oracle XE. If you do decide to go down the MySQL route, try to avoid non-standard data types like SET and ENUM (porting becomes ...



Only top voted, non community-wiki answers of a minimum length are eligible