Database

Eddy Vluggen23-Sep-14 21:10

Eddy Vluggen

23-Sep-14 21:10

dilkonika wrote:
What should i do in this case ?

Consider that they can open Explorer and do things you don't like. Or open the physical PC and do things.

Bastard Programmer from Hell Suspicious | :suss:

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

dilkonika24-Sep-14 3:13

24-Sep-14 3:13

is there a way to protect the mdf file ?

Eddy Vluggen24-Sep-14 6:21

Eddy Vluggen

24-Sep-14 6:21

Not really. The local admin has all the rights on the machine. The person that owns the server, owns the databases, and the data.

Buy a good server, install it SQL Server, put it in a large box, lock it, and hand that to the customer. That way you would be the local admin.

Or host the server from your place, and have them connect to your "SQL Cloud".

Bastard Programmer from Hell Suspicious | :suss:

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

dilkonika24-Sep-14 13:30

24-Sep-14 13:30

if you have read my case , what can I do with "Model" file that is offline ? Someone can get and restore to another PC.
I read somewhere about encrypting the database.
Is this true and how can be done.Can this be a help in my situation ?

Eddy Vluggen25-Sep-14 1:53

Eddy Vluggen

25-Sep-14 1:53

You can encrypt the sprocs, and/or the data. Both will not protect your database-schema.

Bastard Programmer from Hell Suspicious | :suss:

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

Corporal Agarn24-Sep-14 4:33

Corporal Agarn

24-Sep-14 4:33

You can setup the system to only be accessed through stored procedures that use "execute as".

Then just give the permissions that they need to execute the stored procedures. This may mean you need to create a role then grant execute.

dilkonika24-Sep-14 6:18

24-Sep-14 6:18

But what about the "Model" file that is offline ? Someone can get and restore to another PC.
I read somewhere about encrupting the database.
Is this true and how can be done.Can this be a help in my situation ?

data modeling guy29-Sep-14 6:58

data modeling guy

29-Sep-14 6:58

Dilkonika,

One approach is to NOT give users direct access to the machine where database system is running.

Have a middle tier server application(eg: tomcat) hiding your database from end users. Users may connect to middle tier server and invoke services. You may implement authentication using some standard framework within middle tier server application.

Best of luck!

SQL Server : Multiple relationship for the same field

Wendelius30-Sep-14 8:59

Wendelius

30-Sep-14 8:59

If I understood your question correctly, you're worried about several, different things. As Mycroft said, the topic is way too large to discuss extensively in a post. However, few things I'd like to point out:

Use proper security definitions on the server file system. No-one but admins should be let to operate with the files. Sql Server users don't need access to files
See: http://msdn.microsoft.com/en-us/magazine/cc982153.aspx[^]
Use either Sql Server or Windows authenticated logins along with proper database roles to limit the statements
See: http://msdn.microsoft.com/en-us/library/ms189121.aspx[^]
and http://msdn.microsoft.com/en-us/library/ms187965.aspx[^]
Use different logins for admins
If needed, encrypt the files
See: http://msdn.microsoft.com/en-us/library/bb934049.aspx[^]

dilkonika23-Sep-14 7:29

Re: SQL Server : Multiple relationship for the same field

23-Sep-14 7:29

Hello !

I have this situation :

Table : Article

Id

name

category

.........................

Table : Services

id

name

nr

value

-------------------------

Table : Sell_item

Id

item_id

quantity

price

value

----------------

Now the problem is this : The field Item_id on the sell_item table can be the id for an Article or a Service. So I need to create a double relationship for this field one with Article table an one with Service table.

Is this possible ?

If not , or if I'm wrong how can I solve this situation ?

Thank you.

Wendelius23-Sep-14 8:04

Wendelius

23-Sep-14 8:04

If I understood your question correctly you would use the same field (item_id) for two separate purposes. If that's correct, in my opinion you have something wrong with the database model.

Concerning your example I'd suggest something like:

Product
-------
- Id
- name
- category
- IsService
- nr
- value

Sell_item
---------
- Id
- product (former item_id), foreign key to Product
- quantity
- price
- value

The product table would hold all sell able items and the fields are a combination from your both former tables.

Also I wouldn't store the value (at least not as a normal column) since it can always be calculated from quantity times price.

Re: SQL Server : Multiple relationship for the same field

dilkonika23-Sep-14 8:14

Re: SQL Server : Multiple relationship for the same field

23-Sep-14 8:14

ok , but as I can see you have merged my 2 tables. Me to I have thinked this , but the problem is that in my post I have included only some of the fields from 2 tables. In reality these tables have 15 and 13 fileds , and do you think is a good choice to have a table with 28 fields where for each record only 15 or 13 fields will have the data and others are just unnecessary ?

Can be COALESCE a help in this situation ?

Wendelius23-Sep-14 8:33

Wendelius

23-Sep-14 8:33

In my opinion it's ok to have merge the tables and have nullable columns as long as the data is suitable concerning the table purpose. However, without seeing the whole model, it's impossible to say if this is feasible solution in your case.

As a rule of thumb one column stores data only for one purpose. What I mean is that even though you can do a dual reference, it's not advisable.

Another approach could be to use a whole separate table for the links. Consider the following

Article 
-------
Id
Name
Category

Services
-------- 
id
name
nr
value

Sell_action
--------- 
sell_Id
other possible fields describing a single selling action

ArticlesSold
--------- 
sell_Id
article_id
amount
price

ServicesSold
--------- 
sell_Id
service_id
amount
price

Again this should be verified against the whole model and I must admit, I would put all the effort in modeling the sell able products properly instead of thinking how to simultaneously reference two separate tables. That would keep the model simple and clear Smile | :)

Re: SQL Server : Multiple relationship for the same field

Mycroft Holmes23-Sep-14 14:19

Mycroft Holmes

23-Sep-14 14:19

In the rare time when I have had to do this I live without the FK, it is not possible to have the FK to both child tables.

I suspect your 28 field count on the merged table is not valid, there should be some cross over (description in both tables). I would also have no compunction about merging the tables and having some nullable fields as Mika suggested.

Never underestimate the power of human stupidity
RAH

Re: SQL Server : Multiple relationship for the same field

data modeling guy29-Sep-14 5:02

data modeling guy

29-Sep-14 5:02

Dilkonika,

This is a typical supertype-subtype situation. I would consider ITEM as a supertype and ARTCLE and SERVICE as subtypes.
Common attributes of ARTICLE and SERVICE should go to ITEM and there should be identifying relationship from ARTICLE and SERVICE to ITEM.

I dont know how to attach an ER Diagram hence pasting a DDL for the schema.

CREATE TABLE [ITEM]
(
[item_id] char(18) NOT NULL PRIMARY KEY,
[name] char(18) NULL
)
go
CREATE TABLE [SERVICE]
(
[item_id] char(18) NOT NULL PRIMARY KEY,
[nr] char(18) NULL ,
[value] char(18) NULL
)

go
CREATE TABLE [ARTICLE]
(
[item_id] char(18) NOT NULL PRIMARY KEY,
[category] char(18) NULL
)
go
CREATE TABLE [SELL_ITEM]
(
[id] char(18) NOT NULL PRIMARY KEY,
[quantity] char(18) NULL ,
[price] char(18) NULL ,
[value] char(18) NULL ,
[item_id] char(18) NULL
)
go
ALTER TABLE [SERVICE]
ADD CONSTRAINT [R_2] FOREIGN KEY ([item_id]) REFERENCES [ITEM]([item_id])
go
ALTER TABLE [SELL_ITEM]
ADD CONSTRAINT [R_3] FOREIGN KEY ([item_id]) REFERENCES [ITEM]([item_id])
go
ALTER TABLE [ARTICLE]
ADD CONSTRAINT [R_1] FOREIGN KEY ([item_id]) REFERENCES [ITEM]([item_id])
go

Add primary Key as GUID automatic generation

MrKBA22-Sep-14 21:31

22-Sep-14 21:31

I have Some table in database (Access, SQLServer and Oracle) haven' t Primary Key.Client need to do a replication for this database.

I ask how do the update script for one table to add primary key as GUID automatic (it means when I add a record as before there is no need to edit source code)

I did this for SQLServer and it works but for Access and Oracle no idea how to do this Frown | :(

example :

SQL

create table  [SAC_MEMBERSHIPS](
   id_group nvarchar(40) FOREIGN KEY REFERENCES SAC_SUBJECTS(id_subject),
   id_user nvarchar(40) FOREIGN KEY REFERENCES SAC_SUBJECTS(id_subject),
   id_memberships UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY
)/

ALTER TABLE [SAC_MEMBERSHIPS]
ADD [id_memberships] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY
ON [PRIMARY]
/

Kornfeld Eliyahu Peter22-Sep-14 22:00

22-Sep-14 22:00

SQL

create table [table] (
  id raw(32) default sys_guid(),
  ...,
  constraint pk_id primary key (id)
);

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

MrKBA22-Sep-14 22:52

22-Sep-14 22:52

Thank you for that.
But what about Access database ?

Kornfeld Eliyahu Peter22-Sep-14 23:08

22-Sep-14 23:08

I don't know him!

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

MrKBA22-Sep-14 23:40

22-Sep-14 23:40

I talk about Microsoft Office access

Kornfeld Eliyahu Peter22-Sep-14 23:42

22-Sep-14 23:42

Please do not use rude words here...

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

MrKBA23-Sep-14 0:01

23-Sep-14 0:01

sorry if I did a mistake

Kornfeld Eliyahu Peter23-Sep-14 0:02

23-Sep-14 0:02

The only mistake you did that you didn't realized the joke icon Laugh | :laugh:

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

Estys23-Sep-14 0:24

Estys

23-Sep-14 0:24

For ms access the best approximation is

id_memberships AUTOINCREMENT PRIMARY KEY

Cheers

MrKBA24-Sep-14 4:16