Database

Re: What should be the choice.. MongoDB or Sql Server?

3-Sep-15 8:23

Schatak wrote:
I am going to start a big construction project with MVC. I am confused about Db
choice.

I would recommend postponing the choice.

Schatak wrote:
Although worked with SQL server from past 5 years. but now thinking to use
MongoDB.

They are not the same, and one would perform poor in the area where the other excells. SQL Server would be preferred for relational data, especially if you are on a Microsoft stack.

I cannot determine whether or not MongoDB supports SQL92. It would be nice to know, since;

You could postpone the choice of which database-class to use by programming against the IDbConnection interface.
Inject any database-driver in there
And if you keep your SQL compatible with SQL92, then any database supporting the standard could be used.

SQL92 is not as rich as the Sql dialect of the server, but it does offer the advantage to swap out the database entirely, simply by changing the configuration.

..but in general, you take risks in small and simple projects; it would be very not fun if you had to make a lot of changes in a very large project after some months of work, and breaking it in the process.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Jörgen Andersson3-Sep-15 9:14

Jörgen Andersson

3-Sep-15 9:14

Eddy Vluggen wrote:
I cannot determine whether or not MongoDB supports SQL92

There's a reason it's called NoSQL.
NoSQL Explained[^] by Mongo themselves.

Wrong is evil and must be defeated. - Jeff Ello

Re: What should be the choice.. MongoDB or Sql Server?

Eddy Vluggen3-Sep-15 10:08

Re: What should be the choice.. MongoDB or Sql Server?

3-Sep-15 10:08

Yes, I opened multiple search results in multiple tabs, and the first contained claims to be SQL92 compatible; but they were ODBC-based commercial solutions.

Believe that it was wikipedia that stated there was no support for joins Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Jörgen Andersson3-Sep-15 9:16

Jörgen Andersson

3-Sep-15 9:16

I would use what I know best.

Wrong is evil and must be defeated. - Jeff Ello

Re: What should be the choice.. MongoDB or Sql Server?

Schatak3-Sep-15 20:30

Schatak

3-Sep-15 20:30

Right, lets see if my head also think so Smile | :)

Re: What should be the choice.. MongoDB or Sql Server?

jschell11-Sep-15 12:17

jschell

11-Sep-15 12:17

Schatak wrote:
Although worked with SQL server from past 5 years. but now thinking to use MongoDB.

Based on what you posted - bad idea.

Only thing that mongodb might be really good at, starting from zero, is storing and retrieving streaming video. And if if you need that then use mongo for that and use the SQL server for everything else.

Re: What should be the choice.. MongoDB or Sql Server?

Herman<T>.Instance23-Sep-15 0:21

Herman<T>.Instance

23-Sep-15 0:21

Define BIG Construction....

How much data is involved (written to and read from)?
Mainly simple tables with simple types of bugger and heavier types?
You like to encode/decode JSON all the time?

Which requirements are wanted?

In Word you can only store 2 bytes. That is why I use Writer.

Powershell backup and restore SQLscripts

Stephen Holdorf2-Sep-15 13:01

Stephen Holdorf

2-Sep-15 13:01

I am not very familiar with Powershell. I have been studying how it works with SQL server. I have a few simple scripts but am hoping there are some libraries of cmdlets writen that allow the backup and restore from an ASP . Net application.

Re: Powershell backup and restore SQLscripts

Eddy Vluggen3-Sep-15 8:28

3-Sep-15 8:28

Wouldn't it be easier to execute either as a simple command, as opposed to invoking a powershell script to achieve the same?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

About Triggers

dili12341-Sep-15 20:18

dili1234

1-Sep-15 20:18

This is my first time of creating a trigger..I tried to create trigger using mysql to count how many "2007" data are in the table when inserting new record but it not working properly it wont display how 2007 data are in my table.......

My table have more than 50 data...

CREATE TRIGGER No_of_Publisher_year Before Insert ON Book FOR EACH ROW SET @Count_No=New.Copyright;
SET @Count_NO=0;
INSERT INTO Book VALUES(14,'Programming Perl','978-0-201-70073-2','WI','2007-12-11',25.23,2,2007);
SELECT Count(@Count_NO) AS 'No_of_publisher_in_year_2007' FROM Book WHERE Copyright=2007

Re: About Triggers

GuyThiebaut1-Sep-15 22:18

GuyThiebaut

1-Sep-15 22:18

dili1234 wrote:
how many "2007" data are in the table when inserting new record

dili1234 wrote:
CREATE TRIGGER No_of_Publisher_year Before Insert

The before keyword may be why you are not seeing the results you expect.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Re: About Triggers

dili12347-Sep-15 20:28

dili1234

7-Sep-15 20:28

But though i changed Before into after Nothing happened.I want to see the output of the result how just after inserted new value regarding the query.... Frown | :(

Composite Key

Gilbert Consellado31-Aug-15 22:26

31-Aug-15 22:26

Just a dumb question about composite primary key.

I am designing a DB, then i have a specific problem that having 2 primary keys can only solve.

Now I am wondering if having 2 primary keys could hurt performance. Could it be?

I am using MySQL 5.6, and in MyISAM engine.

Any advice will be appreciated.

Thank you

Eddy Vluggen31-Aug-15 23:02

31-Aug-15 23:02

There cannot be two primary keys; such is always a design-error.

You can have multiple fields that uniquely identify the field, but only one of them will be the actual primary key. The others are 'candidate keys', 'unique constraints'.

No, you don't need a primary key to define a relation, but I would urge to not to. And yes, it will hurt performance.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Gilbert Consellado31-Aug-15 23:39

31-Aug-15 23:39

Ohh I see, thank you.

But why MySQL accept this query

SQL

CREATE TABLE IF NOT EXISTS `tbl_sample`
(
  `tbl_sample_id` INT UNSIGNED NOT NULL,
  `tracked_id` INT UNSIGNED NOT NULL,
  -- other columns goes here,
  PRIMARY KEY (`tbl_sample_id`, `tracked_id`)
)ENGINE = MyISAM

what are you referring about "design-error"? do you mean the design standard or the DBMS itself?

Thank you

Eddy Vluggen1-Sep-15 0:18

1-Sep-15 0:18

Gilbert Consellado wrote:
But why MySQL accept this query

..because it defines a single key, which happens to consist of two fields. It is still a single primary key.

Gilbert Consellado wrote:
what are you referring about "design-error"?

I mean that there can only be one "primary".

Having multiple fields in a primary key is quite common. Yes, a larger key has a slight performance penalty; it has to check more data than in a single field, and any index containing the key will also grow.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Gilbert Consellado1-Sep-15 0:38

1-Sep-15 0:38

last question, which is better of the two?

#1

SQL

CREATE TABLE IF NOT EXISTS `tbl_sample`
(
  `tbl_sample_id` INT UNSIGNED NOT NULL,
  `tracked_id` INT UNSIGNED NOT NULL,
  -- other columns goes here,
  PRIMARY KEY (`tbl_sample_id`, `tracked_id`)
)ENGINE = MyISAM

SQL

CREATE TABLE IF NOT EXISTS `tbl_sample`
(
  `tbl_sample_id` INT UNSIGNED NOT NULL,
  `tracked_id` INT UNSIGNED NOT NULL,
  -- other columns goes here,
  PRIMARY KEY (`tbl_sample_id`),
  INDEX `track` (`track_id` ASC)
)ENGINE = MyISAM

Eddy Vluggen1-Sep-15 1:37

1-Sep-15 1:37

The upper uses two fields to identify a single record. The lower one uses a single field; they would allow different things.

I'd recommend to use the smallest set of fields required. Imagine you are the end-user, how do you differentiate between each record? What is it that makes a sample unique?

If a sample can only be identified by giving both a tbl_sample_id and a track_id, they'd need both be part of the key. If either can be used, then the smallest would become the key, and the other a unique index (the upper in the first case, lower in the second case).

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Gilbert Consellado1-Sep-15 3:04

1-Sep-15 3:04

Got it, Thank you

Eddy Vluggen1-Sep-15 3:33

1-Sep-15 3:33

You're welcome Smile | :)

Tim Carmichael1-Sep-15 2:15

Tim Carmichael

1-Sep-15 2:15

Agree with Eddy... and, if you're of an age, think of "Highlander"... there can be only one.

Gilbert Consellado1-Sep-15 3:08

1-Sep-15 3:08

ohh, but I don't know what Highlander is Confused | :confused:

Tim Carmichael1-Sep-15 3:59

Tim Carmichael

1-Sep-15 3:59

Highlander - a 1986 sci-fi movie. If you watch it, the comment will become clear.

Wendelius1-Sep-15 3:07

Wendelius

1-Sep-15 3:07

Agree with Eddy Vluggen. To add a few things:

Personally I would define any column combinations that are unique in real world as unique, natural keys, not as primary keys.
Beyond that I would define a single columns surrogate key for all tables and always fetch/modify rows based on this surrogate key.

For more information, see Surrogate key[^]

Gilbert Consellado1-Sep-15 3:41