Determining Security: column

Showing posts with label column. Show all posts

Thursday, March 29, 2012

Determine Table's PK Columns

I want to do a query on a db's system tables to determine what column(s) are
part of the primary key.
Example.
Code:
SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
sysobjects.name='tablename'
Yields all the column names. But I just want to see those that are part of
the primary key. How?
Ryan
> SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
> sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
> sysobjects.name='tablename'
shuldn't you use information_schema?
.~. Might, Courage, Vision. Sincerity. http://www.linux-sxs.org
/ v \
/( _ )\ (Ubuntu 5.10) Linux 2.6.14.3
^ ^ 22:37:07 up 9 days 2:31 load average: 3.12 1.63 0.80|||Try:
select
*
from
information_schema.constraint_column_usage
where
table_name = 'Order Details'
and objectproperty (object_id (constraint_name), 'IsPrimaryKey') = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:037F8848-C843-4A77-B385-6994E6235CDA@.microsoft.com...
>I want to do a query on a db's system tables to determine what column(s)
>are
> part of the primary key.
> Example.
> Code:
> SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
> sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
> sysobjects.name='tablename'
> Yields all the column names. But I just want to see those that are part of
> the primary key. How?
> Ryan|||Thank you both of you. Yes, Man-wai Chang, the information_schema looks lik
e
it's a lot easier than joining the system tables like I was doing. Thank
you, both.

Determine table names and column names at runtime?

I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.

Thanks.The only advice I can give is this: All your database's objects are stored in the sysobjects table. And then there's syscolumns and sysindexes (shouldn't that be sysindices?).

I would be suprised if there were not free libraries out there that make it easy to get information about sql objects. I don't know of one particularly. Anyone know of one? If not, that might make a cool community project.|||There is a stored procedure sp_tables that returns a list of tables.

There is a stored procedure sp_columns that returns a list of Columns.

These are better to use than the system tables, as they are documented and should not change in a way to break your code. Look them up in Books Online...|||Gravy!

Glad you're here.|||> There is a stored procedure sp_tables that returns a list of tables.

ah, forgot about that. I guess because I usually only use it to do an

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'whatever')

and an SP is no good there.... cheers for reminding me.|||In that case, you could/should use:


IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_name = 'whatever')

Terri

Tuesday, March 27, 2012

Determine rowguid in column and edit column information ASP.NET 1.1 C#

What query should I run?

1. To determine whether a column is a rowguid or not using C# .NET 1.1

2. To add/modify column information and be able to set/change:

- Primary key

- Column Name

- Data Type

- Length

- Allow Null

- Default value

- Precision

- Scale

- Identity

- Identity Seed

- Identity Increment

- Row guid

Thanks a lot!

(1) SELECT OBJECTPROPRTTY(<tableId>,TableHasRowGuidCol) will tell you if the table has uniqueidentifier column.

(2) Please refer BOL.

|||

Hi, thanks for the answer but I tried to run this:

SELECT

OBJECTPROPERTY(OBJECT_ID('temp_aspnet_Permissions'),TableHasRowGuidCol)

and it gives me error:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'TableHasRowGuidCol'.

|||

Never mind, I forgot about the quote:

SELECT

OBJECTPROPERTY(OBJECT_ID('temp_aspnet_Permissions'),'TableHasRowGuidCol')

Thanks again!

|||Btw, it only returning number 1 or 0, I need to know which column that has rowguid, so it still hasn't answer my question :(|||

I found it, you need to use columnproperty. Thanks again for the guidance!

SELECTCOLUMNPROPERTY(OBJECT_ID('testcol2'),'col2','IsRowGuidCol')

|||

SELECT

COLUMNPROPERTY(id,name,'IsRowGuidCol'),*

FROMsyscolumnsWHEREid=Object_Id('yourtable')

andCOLUMNPROPERTY(id,name,'IsRowGuidCol') = 1

Determine Late from Time

I have a datetime column named Timesheet.StartTime and a datetime column
also named Employee.StartTime and I want to compare the time portion of each
to see if the Timesheet.StartTime is later than the Employee.StartTime. My
problem is that I only need to compare the time portion as the date on
Employee.StartTime is constant. Can anyone give me a tip or where I can
find a solution? Thanks.
DavidCan you give some example?|||Even if you need only time portions - assuming your date portions are
the same, all you need is the difference between the two dates.
Datediff function can exactly give you the difference in hours, mins or
sec. Hope this helps.
Have a look at the following code
declare @.date1 datetime,
@.date2 datetime
set @.date1 = '2006-02-14 13:40:07.603'
set @.date2 = '2006-02-14 15:40:07.603'
select datediff(hh,@.date1,@.date2)
select datediff(mi,@.date1,@.date2)|||Try using datepart() function. This can give you the exact part of the
datetime variable you are after.|||Use datediff
declare @.d1 datetime,
@.d2 datetime
set @.d1 = '2006-02-13 11:44:07.102'
set @.d2 = '2006-02-13 12:44:07.102'
select datediff(ss,@.d1,@.d2)
result is 3600
select datediff(ss,@.d2,@.d1)
result is -3600
so if first date is less than or equal to second date then result will
be >= 0 else it will be < 0
Regards
Amish Shahsql

Sunday, March 25, 2012

Determine if a column is indexed (and the index name)

Is there a slick way to determine if all the indexes (if any) for a particular field?

let's say:

tbPeople

ID (bigint)
FirstName (varchar(50))
LastName (varchar(50))

I need to determine if [FirstName] in indexed anywhere, and if so, what are the name(s) of the indexes.

thanks ahead of time.

Which version of SS are you using?

To select all indexes where the column is part of the key, in 2005, you can use:

select

object_name(si.[object_id])as table_name,

si.name as index_name,

index_col(object_name(sic.[object_id]), sic.index_id, sic.index_column_id)as column_name,

sic.key_ordinal,

sic.is_descending_key,

sic.is_included_column

from

sys.indexesas si

innerjoin

sys.index_columnsas sic

on si.[object_id] = sic.[object_id]

and si.index_id = sic.index_id

where

si.object_id=object_id('dbo.Employees')

andcolumnproperty(object_id('dbo.Employees'),'LastName','ColumnId')= sic.column_id

orderby

sic.[object_id],

sic.index_id

AMB

|||

Brilliant. Thanks. Was not aware of sys.index_columns.

Any cute tricks for the equivalent query on SS 2K?

|||

Try:

select

object_name(si.[id])as table_name,

si.name as index_name,

col_name(sic.[id], sic.colid)as column_name,

sic.keyno

from

sysindexesas si

innerjoin

sysindexkeysas sic

on si.[id] = sic.[id]

and si.indid = sic.indid

where

si.[id] =object_id('dbo.Employees')

andcol_name(sic.[id], sic.colid)='LastName'

orderby

sic.[id],

sic.indid

AMB

Thursday, March 22, 2012

determin name of primary column

given the name of any table how can i programmatically get the name of the primary key column?

You can use the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view like:

SELECT tc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc

WHERE tc.TABLE_SCHEMA = 'dbo'

and tc.TABLE_NAME = '<your_table>'

and tc.CONSTRAINT_TYPE = 'PRIMARY KEY';

sql

Wednesday, March 21, 2012

Detecting Carriage Returns in a column

Can anyone provide me with some SQL that will identify rows from a table where a varchar column named "Notes" contain Carriage Returns?

I know that with report writer SQR I can translate CR's to white space but I do not know of any Sybase function that will allow me to do the same, any ideas on this well would be appreciated.For Microsoft SQL Server, I'd use CharIndex. For Sybase, it would depend on which of the Sybase servers you are using. Different Sybase engines have different string handling syntaxes.

-PatP|||Thanks Pat. One other dumb question is how is a Carriage Return represented in SQL? char[13]?? THanks again for your help, I used to know this but I'm a little rusty.|||That depends on what you mean by a carriage return. ;)

The problem is complex, because different operating systems store the "line end" marker differently, and because different SQL implementations have different ways of dealing with character expressions.

In Microsoft SQL Server, you represent a lone carriage return character asChar(13)...but, based on your previous question I think you really want to find a "line end" instead of a carriage return character. If you want to find a "line end" as used in Microsoft SQL Server, then you want:Char(13) + Char(10)

-PatP

Detect Missing Records in Flat File

I am importing records from a flat file to a database table. If a record is in the table but NOT in the flat file, I need to update a date column in the table.

Any ideas?

First page of this forum, "Checking to see if a record exists, if so update, else insert"

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Phil, I read that post before asking my question.

Since the row exists in the table but NOT in the flat file, a lookup doesn't work. Unless you know of a way to connect a Lookup to a flat file. As I mentioned, I need to know when a row in the database doesn't exist in a flat file.

|||A lookup does too work, you just have to load a staging table first with the contents of the flat file.

You can have more than one data flow task in a given package, so in the first data flow, load up the staging table with the file, and then in the second data flow, use an OLE DB source against your table and then use the lookup transformation against your staging table.|||

Thanks again Phil.

I seemed to have accomplished the same thing by using a Merge Join setup as a left outer join.

Do you see any performance advantages to using the staging table with a lookup instead?

|||

skaszyk wrote:

Thanks again Phil.

I seemed to have accomplished the same thing by using a Merge Join setup as a left outer join.

Do you see any performance advantages to using the staging table with a lookup instead?

If the file is hugh, something on the order of +1,000,000 rows, I'd say yes, but flat files are very fast to process, and I would not think you'd gain anything by using your method versus mine. For most things I do, though, I always push data into staging tables first, before working with the data. Having the data in a table offers the ability to perform SQL upfront while leveraging the database engine, among other things.|||

dear friend,

In my opinion, you can do that using a MERGE JOIN.

Add 2 sources, one OLEDB Datasource in order to get all the IDs, and a second to get the records from flatfile.

Add a merge join and configure it as right or left join depend on the sources order.

Helped?

Regards!

|||

PedroCGD wrote:

dear friend,

In my opinion, you can do that using a MERGE JOIN.

Add 2 sources, one OLEDB Datasource in order to get all the IDs, and a second to get the records from flatfile.

Add a merge join and configure it as right or left join depend on the sources order.

Helped?

Regards!

Pedro, he's already done that. Please read the entire thread first.|||

Sorry phil, but my page wasn't refreshed and didn't see some posts...

Regards!

Wednesday, March 7, 2012

destination table not exist while adding column in published artic

I used the add_relpcolumn to add column in the published table but not
knowing that the subscriber database does not have the table. I found the
error on the distribution agent about 'Not able to alter the table because
the table doesn't exist', which explained what happened. As a result, it
breaks the replication. How do I fix the problem to make the replication
going again?
Thanks for any help in advanced
This sounds like a bug, could you post your publication script?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:FBAB2D4B-3DF5-47E5-ABF2-FF6634910114@.microsoft.com...
>I used the add_relpcolumn to add column in the published table but not
> knowing that the subscriber database does not have the table. I found the
> error on the distribution agent about 'Not able to alter the table because
> the table doesn't exist', which explained what happened. As a result, it
> breaks the replication. How do I fix the problem to make the replication
> going again?
> Thanks for any help in advanced

Designing reusable column transformations?

We receive thousands of files every week from various clients and we attempt to clean the columns using the same technique over and over so the data is consistent. The problem is I dont see a way to reuse complex column transformations in different packages. I would hate to have to go change every package if we change the rules for cleaning a column.

So #1: Can you create some kind of script or .net function that cleans a column and reuse it in multiple packages (or even in the same package)?

#2: Is it possible to call functions from the Derived Column expression builder?

Thanks!

1)May be. You always can use a script component with the transformation rules and copy and paste it many times; it that sounds reasonable to you.

2) No you can not.

|||

Craig, I have had do do the same thign.The best solution is not a script component, as the person above mentioned.

Take the hit, build a custom component. Once you get it built, adding a new column is as simple as adding a new column to the transform.

You will want to build a custom pipline component. Both Wrox and O'riely have some decent chapters decribing how to do this. and there is definately info in microsoft.

I have 350 packages, that all chagne time from european time to us time, in the data flow. By writing 1 component, once, I was able to then delegate the rest of the work, since the hard part was encapsulated in a compoent.

good luck

Saturday, February 25, 2012

designing for both pdf and excel rendering

A report I designed looked perfect html and pdf but when exported to excel it chops off the last column when printed. Does the report grow when exported to excel? How can I fix this? If I change the margins it fixes on the excel side but then the page doesn't look right on the pdf side. Thank you in advance for any help.Microsoft any suggestions please.

Designating an identity column when you already have primary key

Hello --

Following normal practice, I have an autoincrementing identity column designated as primary key in my table. I have two other columns that should also contain unique values per record, but the Identity option is greyed out (in Management Studio) for all columns other than the primary key.

I'm enforcing this programmatically (in my C# code) at this point, but I'd like to back that up with a constraint in the database itself.

Any help is appreciated.

Eric

Hello my friend,

Turn Allow Nulls off for these other 2 columns. In addition to this, here is some SQL you can run at any time to check for duplicate values: -

SELECT ErrorTypeID, COUNT(*) AS Counter
FROM TBLERROR
GROUP BY ErrorTypeID
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Kind regards

Scotty

|||tblErrror is a custom table in my database. Substitute it for your table and the field you want to check|||

Thanks, Scotty.

Is there any way to apply manual constraints so the duplicate value doesn't end up in the table in the first place, or would I instead have to insert new rows through a stored procedure and apply the check/abort in there?

|||

Hello again my friend,

Put this index on your table: -

CREATE UNIQUE INDEX MyIndexName ON MyTableName (MyFieldName)

If there are duplicates already, the above statement will fail. If there are not, it will work and if someone, or some code, tries to insert a duplicate, the record will not be inserted/updated and an error will result.

Kind regards

Scotty

|||

If you want to remove an index you have added, do the following: -

DROP INDEX MyTableName.MyIndexName

Kind regards

Scotty

|||

EricLaszlo:
Hello --
Following normal practice, I have an autoincrementing identity column designated as primary key in my table. I have two other columns that should also contain unique values per record, but the Identity option is greyed out (in Management Studio) for all columns other than the primary key.
I'm enforcing this programmatically (in my C# code) at this point, but I'd like to back that up with a constraint in the database itself.
Any help is appreciated.
Eric

The IDENTITY property can generate auto numbers for your column however it does not guarantee uniqueness because IDENTITY can generate gaps, so Microsoft recommend you use GUID instead. But you cannot use GUID because you have three columns that need to be Unique so that leave only one option Unique constraint because you can define more than one Unique constraint in a table. I have seen table definitions with many constraints so you could run some tests in relation to ADO.NET as needed for your application. Try the link below for details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms191166.aspx

|||

Thank you both very much for your input.

Based on that, I was able to apply the contstraints with the following syntax:

ALTER

TABLE Profiles

ADD

CONSTRAINT ncEmail

UNIQUE

NONCLUSTERED(Email)

Having read a little about clustered indices in my travels, I tried to apply one to my Id column - didn't notice that this column already had a clustered index - not sure if that was from designating is as and Identity column or applying primary key.

In any case, I think I'm good for now...

Friday, February 24, 2012

Design Question: column storing a type

Hi,
I'm 50/50 about this design topic, could someone please shed some light?
thanks!
I often have to add a column to our db just to store a permission type of a
user etc... In the code (C#), it should be enum type to UI level; in the
db, I am not sure which of these 2 ways is "generally" a better design:
1) varchar type with check constraint to make sure certain type description
can be stored.
2) int type with or without check constraint (without would allow the code
to extend the enum type without changing db)
I know that (2) is a bit faster and take less space but it takes a long time
to look up some info (imagine if we have 50 of these types through out the
system). (1) would give us a better context by run a sql statement, it would
be harder to make reading mistake and bug in stored proc because it's highly
descriptive.
Thanks!!"Zester" <zeze@.nottospam.com> wrote in message
news:O5KWfW1OIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I'm 50/50 about this design topic, could someone please shed some light?
> thanks!
> I often have to add a column to our db just to store a permission type of
> a user etc... In the code (C#), it should be enum type to UI level; in
> the db, I am not sure which of these 2 ways is "generally" a better
> design:
> 1) varchar type with check constraint to make sure certain type
> description can be stored.
> 2) int type with or without check constraint (without would allow the code
> to extend the enum type without changing db)
> I know that (2) is a bit faster and take less space but it takes a long
> time to look up some info (imagine if we have 50 of these types through
> out the system). (1) would give us a better context by run a sql
> statement, it would be harder to make reading mistake and bug in stored
> proc because it's highly descriptive.
> Thanks!!
>
Or
3) A column with a FOREIGN KEY referencing a PermissionType table.
If you are likely to modify the set of types frequently then go for 3)
because that way you can easily use the PermissionType table to drive the
options available in your app without any code change.
If you are happy to make schema and code changes whenever the set of types
changes then use 1).
Not certain what your intention is with 2). I think you mean a surrogate
key, which is a differrent question altogether and one that doesn't have a
simple answer. I suggest you consult your DBA / Database Architect.
--
David Portas|||For (2), I meant that we just stored the enum value in the form of the
integer without referencing to the definition table (which is option 3 you
pointed out). For example, in C/C# code
enum AllowPrintPermission
{
None, // never allow = 0
AllowPrintPublicForms, // = 1
AllowPrintAllForms // = 2
}
This type will be stored as 0,1,2 respectively
(3) would result in many tables for us. These types are not shared by
multiple tables.
We have lots of user permissions, it's a con to do many joints to get the
meaning, sometimes the sql statement can get so complex that data mining and
debugging tasks down the road can be a high cost.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uJuYZe1OIHA.4476@.TK2MSFTNGP06.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:O5KWfW1OIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I'm 50/50 about this design topic, could someone please shed some light?
>> thanks!
>> I often have to add a column to our db just to store a permission type of
>> a user etc... In the code (C#), it should be enum type to UI level; in
>> the db, I am not sure which of these 2 ways is "generally" a better
>> design:
>> 1) varchar type with check constraint to make sure certain type
>> description can be stored.
>> 2) int type with or without check constraint (without would allow the
>> code to extend the enum type without changing db)
>> I know that (2) is a bit faster and take less space but it takes a long
>> time to look up some info (imagine if we have 50 of these types through
>> out the system). (1) would give us a better context by run a sql
>> statement, it would be harder to make reading mistake and bug in stored
>> proc because it's highly descriptive.
>> Thanks!!
> Or
> 3) A column with a FOREIGN KEY referencing a PermissionType table.
> If you are likely to modify the set of types frequently then go for 3)
> because that way you can easily use the PermissionType table to drive the
> options available in your app without any code change.
> If you are happy to make schema and code changes whenever the set of types
> changes then use 1).
> Not certain what your intention is with 2). I think you mean a surrogate
> key, which is a differrent question altogether and one that doesn't have a
> simple answer. I suggest you consult your DBA / Database Architect.
> --
> David Portas
>|||"Zester" <zeze@.nottospam.com> wrote in message
news:OU%23j2k1OIHA.2376@.TK2MSFTNGP02.phx.gbl...
> For (2), I meant that we just stored the enum value in the form of the
> integer without referencing to the definition table (which is option 3 you
> pointed out). For example, in C/C# code
> enum AllowPrintPermission
> {
> None, // never allow = 0
> AllowPrintPublicForms, // = 1
> AllowPrintAllForms // = 2
> }
> This type will be stored as 0,1,2 respectively
> (3) would result in many tables for us. These types are not shared by
> multiple tables.
> We have lots of user permissions, it's a con to do many joints to get the
> meaning, sometimes the sql statement can get so complex that data mining
> and debugging tasks down the road can be a high cost.
>
If you don't mind breaking normalization rules a bit, then you can do a
modified version of 3.
In the lookup table, include an identifier of some type (TableName for
example).
Then a single PermissionType table could support many tables in your db.
Example:
CREATE TABLE dbo.PermissionType (
PermissionTypeID int IDENTITY(1,1) NOT NULL PRIMARY KEY
SchemaName sysname NOT NULL,
TableName sysname NOT NULL,
PermissionType varchar(100) NOT NULL,
PermissionTypeEnum int NOT NULL)
ALTER TABLE dbo.PermissionType ADD UNIQUE CONSTRAINT UC_PermissionType
(SchemaName, TableName, PermissionType, PermissionTypeEnum)
INSERT PermissionType VALUES ('dbo.', 'Payroll', 'ViewAll', 0)
Rick Sawtell
MCT, MCSD, MCDBA|||"Zester" <zeze@.nottospam.com> wrote in message
news:OU%23j2k1OIHA.2376@.TK2MSFTNGP02.phx.gbl...
> For (2), I meant that we just stored the enum value in the form of the
> integer without referencing to the definition table (which is option 3 you
> pointed out). For example, in C/C# code
> enum AllowPrintPermission
> {
> None, // never allow = 0
> AllowPrintPublicForms, // = 1
> AllowPrintAllForms // = 2
> }
> This type will be stored as 0,1,2 respectively
> (3) would result in many tables for us. These types are not shared by
> multiple tables.
> We have lots of user permissions, it's a con to do many joints to get the
> meaning, sometimes the sql statement can get so complex that data mining
> and debugging tasks down the road can be a high cost.
>
Creating an extra table does not mean you need any more joins or more
complex SQL than before. Use exactly the same queries you would in your
other solutions. Creating an extra table may just make it easier to maintain
the set of values. "Many tables" should not pose any kind of problem that I
can see.
Of course there is no single "right" answer. Just my 0.02
--
David Portas|||Thanks for pointing to a new direction; so what type would the column in
payroll table be? int? how does it reference (via foreign key) to the
Permission table when the value is not a primary key in Permission table?
If there is no connection via foreign key to maintain the integrity of the
relationship, I don't see the benefit of this approach. Could you please
explain? thanks!!
"Rick Sawtell" <r_sawtell@.nospam.hotmail.com> wrote in message
news:OleRx31OIHA.5524@.TK2MSFTNGP05.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OU%23j2k1OIHA.2376@.TK2MSFTNGP02.phx.gbl...
>> For (2), I meant that we just stored the enum value in the form of the
>> integer without referencing to the definition table (which is option 3
>> you pointed out). For example, in C/C# code
>> enum AllowPrintPermission
>> {
>> None, // never allow = 0
>> AllowPrintPublicForms, // = 1
>> AllowPrintAllForms // = 2
>> }
>> This type will be stored as 0,1,2 respectively
>> (3) would result in many tables for us. These types are not shared by
>> multiple tables.
>> We have lots of user permissions, it's a con to do many joints to get the
>> meaning, sometimes the sql statement can get so complex that data mining
>> and debugging tasks down the road can be a high cost.
> If you don't mind breaking normalization rules a bit, then you can do a
> modified version of 3.
> In the lookup table, include an identifier of some type (TableName for
> example).
> Then a single PermissionType table could support many tables in your db.
> Example:
> CREATE TABLE dbo.PermissionType (
> PermissionTypeID int IDENTITY(1,1) NOT NULL PRIMARY KEY
> SchemaName sysname NOT NULL,
> TableName sysname NOT NULL,
> PermissionType varchar(100) NOT NULL,
> PermissionTypeEnum int NOT NULL)
> ALTER TABLE dbo.PermissionType ADD UNIQUE CONSTRAINT UC_PermissionType
> (SchemaName, TableName, PermissionType, PermissionTypeEnum)
>
> INSERT PermissionType VALUES ('dbo.', 'Payroll', 'ViewAll', 0)
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Zester" <zeze@.nottospam.com> wrote in message
news:evdwX$1OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Thanks for pointing to a new direction; so what type would the column in
> payroll table be? int? how does it reference (via foreign key) to the
> Permission table when the value is not a primary key in Permission table?
> If there is no connection via foreign key to maintain the integrity of the
> relationship, I don't see the benefit of this approach. Could you please
> explain? thanks!!
1. Use the IDENTITY column in the base tables. You do not have to create a
FK constraint, but it is not a bad idea.
2. Perform joins on that IDENTITY column to get your enum
Rick|||Extra table would bring more joints, right? To use sql statement to find out
user permissions, we would have to do this:
Assume PrintPermission table is defined with 2 columns
PrintPermission
(
PermissionType int primary key not null default( 0 ), check PermissionType
in (0,1,2),
PermissionDesc varchar(50) not null default ( 'NeverAllow' ), check in
('NeverAllow', 'AllowPrintPublicForms', 'AllowPrintAllForms' )
)
select u.UserName, printPerm.PermissionDesc
from User u JOIN PrintPermission printPerm on u.PrintPermissionType =printPerm.PermissionType
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:O1igq91OIHA.4912@.TK2MSFTNGP06.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OU%23j2k1OIHA.2376@.TK2MSFTNGP02.phx.gbl...
>> For (2), I meant that we just stored the enum value in the form of the
>> integer without referencing to the definition table (which is option 3
>> you pointed out). For example, in C/C# code
>> enum AllowPrintPermission
>> {
>> None, // never allow = 0
>> AllowPrintPublicForms, // = 1
>> AllowPrintAllForms // = 2
>> }
>> This type will be stored as 0,1,2 respectively
>> (3) would result in many tables for us. These types are not shared by
>> multiple tables.
>> We have lots of user permissions, it's a con to do many joints to get the
>> meaning, sometimes the sql statement can get so complex that data mining
>> and debugging tasks down the road can be a high cost.
> Creating an extra table does not mean you need any more joins or more
> complex SQL than before. Use exactly the same queries you would in your
> other solutions. Creating an extra table may just make it easier to
> maintain the set of values. "Many tables" should not pose any kind of
> problem that I can see.
> Of course there is no single "right" answer. Just my 0.02
> --
> David Portas
>|||Hi Rick,
what's the benefit for this approach? FK must references a primary key, in
your suggestion, the primary key in PermissionType is PermissionTypeID
(not the possible value of the PrintPermissionType), so it's a lose
relationship. I still don't see the benefit that worth the joining troubles.
thanks!
"Rick Sawtell" <r_sawtell@.nospam.hotmail.com> wrote in message
news:efRNMH2OIHA.5980@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:evdwX$1OIHA.6036@.TK2MSFTNGP03.phx.gbl...
>> Thanks for pointing to a new direction; so what type would the column in
>> payroll table be? int? how does it reference (via foreign key) to the
>> Permission table when the value is not a primary key in Permission table?
>> If there is no connection via foreign key to maintain the integrity of
>> the relationship, I don't see the benefit of this approach. Could you
>> please explain? thanks!!
> 1. Use the IDENTITY column in the base tables. You do not have to create
> a FK constraint, but it is not a bad idea.
> 2. Perform joins on that IDENTITY column to get your enum
>
> Rick
>
>
>|||"Zester" <zeze@.nottospam.com> wrote in message
news:eGke7G2OIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Extra table would bring more joints, right? To use sql statement to find
> out user permissions, we would have to do this:
> Assume PrintPermission table is defined with 2 columns
> PrintPermission
> (
> PermissionType int primary key not null default( 0 ), check
> PermissionType in (0,1,2),
> PermissionDesc varchar(50) not null default ( 'NeverAllow' ), check in
> ('NeverAllow', 'AllowPrintPublicForms', 'AllowPrintAllForms' )
> )
> select u.UserName, printPerm.PermissionDesc
> from User u JOIN PrintPermission printPerm on u.PrintPermissionType => printPerm.PermissionType
>
Compared to what alternative? Either the description is in the database or
it isn't. If it isn't then it's irrelevant whether or not you create an
extra table. No join is necessary:
SELECT u.UserName, u.PrintPermissionType
FROM User;
If you DO want the description in the database then I don't know what
alternative you are proposing.
--
David Portas|||"Zester" <zeze@.nottospam.com> wrote in message
news:uhu1$L2OIHA.5988@.TK2MSFTNGP02.phx.gbl...
> Hi Rick,
> what's the benefit for this approach? FK must references a primary key, in
> your suggestion, the primary key in PermissionType is PermissionTypeID
> (not the possible value of the PrintPermissionType), so it's a lose
> relationship. I still don't see the benefit that worth the joining
> troubles. thanks!
My apologies.. In your primary tables, add the value in the IDENTITY column
from the PermissionType table as a FK.
Example:
CREATE dbo.SomeTableStoringData (
x int PRIMARY KEY,
y varchar(100), -- Some data
z varchar(100), -- Some data
PermissionTypeID int NOT NULL,
CONSTRAINT FK_SomeTableStoringData_PermissionType FOREIGN KEY
(PermissionTypeID) REFERENCES dbo.PermissionType (PermissionTypeID)
)
SELECT
SomeTableStoringData.x,
SomeTableStoringData.y,
PermissionType.Description,
PermissionType.PermissionTypeEnum
FROM
dbo.SomeTableStoringData
JOIN
dbo.PermissionType
ON SomeTableStoringData.PermissionTypeID = PermissionType.PermissionTypeID
-- You can include a WHERE clause to ensure that the correct table
permissions are being looked at. Example:
WHERE PermissionType.PermissionTypeEnum = 2 -- In this example, the enum 2
means ViewAll
Rick Sawtell|||Comparing with option (1); the other options including what you brought
updon't have enough benefits to offset the joining troubles. Of course,
someone outthere might have a few more pros to add to them that can tip the
scale.
Option (1) gives me the descriptions in the db with no joint. I am basically
seeking out strong arguments against it being the best approach (when no
other table would share the type).
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OhmeqN2OIHA.536@.TK2MSFTNGP06.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:eGke7G2OIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Extra table would bring more joints, right? To use sql statement to find
>> out user permissions, we would have to do this:
>> Assume PrintPermission table is defined with 2 columns
>> PrintPermission
>> (
>> PermissionType int primary key not null default( 0 ), check
>> PermissionType in (0,1,2),
>> PermissionDesc varchar(50) not null default ( 'NeverAllow' ), check in
>> ('NeverAllow', 'AllowPrintPublicForms', 'AllowPrintAllForms' )
>> )
>> select u.UserName, printPerm.PermissionDesc
>> from User u JOIN PrintPermission printPerm on u.PrintPermissionType =>> printPerm.PermissionType
>>
>
> Compared to what alternative? Either the description is in the database or
> it isn't. If it isn't then it's irrelevant whether or not you create an
> extra table. No join is necessary:
> SELECT u.UserName, u.PrintPermissionType
> FROM User;
> If you DO want the description in the database then I don't know what
> alternative you are proposing.
> --
> David Portas
>|||So basically, this approach would unify all possible values of all
permission types in the system, right? PermissionTypeID can be 125 when 125
is defined as AllowPrintingPrivateForms. What about if we need to store
another permission for viewing files. That would result in another
column PermissionTypeID2. If a user in our system has 50 permission types,
it would be PermissionTypeID1...50?
Now the need for joining is even higher, right? that is because I can't just
count on my memory as much that PermissionType = 0 means no permission (the
basic default situation) since it would have a value of 124. What's the
benefit? thanks!
"Rick Sawtell" <r_sawtell@.nospam.hotmail.com> wrote in message
news:OPD1vS2OIHA.4272@.TK2MSFTNGP06.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:uhu1$L2OIHA.5988@.TK2MSFTNGP02.phx.gbl...
>> Hi Rick,
>> what's the benefit for this approach? FK must references a primary key,
>> in your suggestion, the primary key in PermissionType is PermissionTypeID
>> (not the possible value of the PrintPermissionType), so it's a lose
>> relationship. I still don't see the benefit that worth the joining
>> troubles. thanks!
>
> My apologies.. In your primary tables, add the value in the IDENTITY
> column from the PermissionType table as a FK.
> Example:
> CREATE dbo.SomeTableStoringData (
> x int PRIMARY KEY,
> y varchar(100), -- Some data
> z varchar(100), -- Some data
> PermissionTypeID int NOT NULL,
> CONSTRAINT FK_SomeTableStoringData_PermissionType FOREIGN KEY
> (PermissionTypeID) REFERENCES dbo.PermissionType (PermissionTypeID)
> )
>
> SELECT
> SomeTableStoringData.x,
> SomeTableStoringData.y,
> PermissionType.Description,
> PermissionType.PermissionTypeEnum
> FROM
> dbo.SomeTableStoringData
> JOIN
> dbo.PermissionType
> ON SomeTableStoringData.PermissionTypeID => PermissionType.PermissionTypeID
> -- You can include a WHERE clause to ensure that the correct table
> permissions are being looked at. Example:
> WHERE PermissionType.PermissionTypeEnum = 2 -- In this example, the enum
> 2 means ViewAll
>
> Rick Sawtell
>
>
>
>|||"Zester" <zeze@.nottospam.com> wrote in message
news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.gbl...
> Comparing with option (1); the other options including what you brought
> updon't have enough benefits to offset the joining troubles. Of course,
> someone outthere might have a few more pros to add to them that can tip
> the scale.
> Option (1) gives me the descriptions in the db with no joint. I am
> basically seeking out strong arguments against it being the best approach
> (when no other table would share the type).
>
A disadvantage of (1) is that it needs a schema change to add a new type.
The advantage of (3) is that it doesn't and it *doesn't* require any extra
joins either compared to (1). But I think I'm just failing to communicate
that second point so it's over to you from here on...
--
David Portas|||so with 50 possible permission types in a system, you would have 50 of these
tables with 2 columns each?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:%23kGdHb2OIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Comparing with option (1); the other options including what you brought
>> updon't have enough benefits to offset the joining troubles. Of course,
>> someone outthere might have a few more pros to add to them that can tip
>> the scale.
>> Option (1) gives me the descriptions in the db with no joint. I am
>> basically seeking out strong arguments against it being the best approach
>> (when no other table would share the type).
> A disadvantage of (1) is that it needs a schema change to add a new type.
> The advantage of (3) is that it doesn't and it *doesn't* require any extra
> joins either compared to (1). But I think I'm just failing to communicate
> that second point so it's over to you from here on...
> --
> David Portas
>|||I totally agree with David here. These codes should each have their own
table with a foreign key constraint. You don't always have to use a
surrogate key for them either, use the name of the permission as the Key and
then you don't have to do any extra joins. If the name is too long you can
usually come up with a unique abbreviation that still conveys the meaning
and you still don't have to do the extra joins.
That said, designing a database around eliminating joins is the wrong
approach. These kind of "lookup" tables are usually pretty small so the
joins are very fast and efficient. Even if they aren't small, rather than
searching for ways to avoid the joins, you can use things such as indexed
views to speed up queries.
Don't compromise data integrity and long term viability for the sake of
saving a few joins.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:%23kGdHb2OIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Comparing with option (1); the other options including what you brought
>> updon't have enough benefits to offset the joining troubles. Of course,
>> someone outthere might have a few more pros to add to them that can tip
>> the scale.
>> Option (1) gives me the descriptions in the db with no joint. I am
>> basically seeking out strong arguments against it being the best approach
>> (when no other table would share the type).
> A disadvantage of (1) is that it needs a schema change to add a new type.
> The advantage of (3) is that it doesn't and it *doesn't* require any extra
> joins either compared to (1). But I think I'm just failing to communicate
> that second point so it's over to you from here on...
> --
> David Portas
>|||Thanks for your input. Hm, that's true that we can just keeping using the
text description in the main table and just create another table to be
referenced. So you would have 50 extra tables, but why data integrity is an
issue when just use check constraint to make sure the set options are
declared and reinforced? The only drawback I see so far is if we need to add
new enum value to the set, we need to change the check constraint instead of
just simply inserting another entry in the permission type definition table.
However, to do the insertion, we need 50 UI pieces. I think there are
something I should point out, we host db solution internally (on-site,
web-based) so modifying the db constraints is an easy thing for us comparing
with releasing db schema with the solution like other product.
"DCPeterson" <sgtp_usmc@.hotmail.com> wrote in message
news:eN04BR3OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>I totally agree with David here. These codes should each have their own
>table with a foreign key constraint. You don't always have to use a
>surrogate key for them either, use the name of the permission as the Key
>and then you don't have to do any extra joins. If the name is too long you
>can usually come up with a unique abbreviation that still conveys the
>meaning and you still don't have to do the extra joins.
> That said, designing a database around eliminating joins is the wrong
> approach. These kind of "lookup" tables are usually pretty small so the
> joins are very fast and efficient. Even if they aren't small, rather than
> searching for ways to avoid the joins, you can use things such as indexed
> views to speed up queries.
> Don't compromise data integrity and long term viability for the sake of
> saving a few joins.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:%23kGdHb2OIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> "Zester" <zeze@.nottospam.com> wrote in message
>> news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Comparing with option (1); the other options including what you brought
>> updon't have enough benefits to offset the joining troubles. Of course,
>> someone outthere might have a few more pros to add to them that can tip
>> the scale.
>> Option (1) gives me the descriptions in the db with no joint. I am
>> basically seeking out strong arguments against it being the best
>> approach (when no other table would share the type).
>>
>> A disadvantage of (1) is that it needs a schema change to add a new type.
>> The advantage of (3) is that it doesn't and it *doesn't* require any
>> extra joins either compared to (1). But I think I'm just failing to
>> communicate that second point so it's over to you from here on...
>> --
>> David Portas
>>
>|||Constraints work for this if the number of valid values is small and
relatively static. I still prefer the use of tables and FK's though. You
don't need to create 50 new UI pieces to update those tables, only those
that will change "frequently". I think it's easier to insert or update
tables as part of a deployment, than to change check constraints...
"Zester" <zeze@.nottospam.com> wrote in message
news:uIE29b3OIHA.3532@.TK2MSFTNGP04.phx.gbl...
> Thanks for your input. Hm, that's true that we can just keeping using the
> text description in the main table and just create another table to be
> referenced. So you would have 50 extra tables, but why data integrity is
> an issue when just use check constraint to make sure the set options are
> declared and reinforced? The only drawback I see so far is if we need to
> add new enum value to the set, we need to change the check constraint
> instead of just simply inserting another entry in the permission type
> definition table. However, to do the insertion, we need 50 UI pieces. I
> think there are something I should point out, we host db solution
> internally (on-site, web-based) so modifying the db constraints is an easy
> thing for us comparing with releasing db schema with the solution like
> other product.
> "DCPeterson" <sgtp_usmc@.hotmail.com> wrote in message
> news:eN04BR3OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>>I totally agree with David here. These codes should each have their own
>>table with a foreign key constraint. You don't always have to use a
>>surrogate key for them either, use the name of the permission as the Key
>>and then you don't have to do any extra joins. If the name is too long
>>you can usually come up with a unique abbreviation that still conveys the
>>meaning and you still don't have to do the extra joins.
>> That said, designing a database around eliminating joins is the wrong
>> approach. These kind of "lookup" tables are usually pretty small so the
>> joins are very fast and efficient. Even if they aren't small, rather
>> than searching for ways to avoid the joins, you can use things such as
>> indexed views to speed up queries.
>> Don't compromise data integrity and long term viability for the sake of
>> saving a few joins.
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:%23kGdHb2OIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> "Zester" <zeze@.nottospam.com> wrote in message
>> news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Comparing with option (1); the other options including what you brought
>> updon't have enough benefits to offset the joining troubles. Of course,
>> someone outthere might have a few more pros to add to them that can tip
>> the scale.
>> Option (1) gives me the descriptions in the db with no joint. I am
>> basically seeking out strong arguments against it being the best
>> approach (when no other table would share the type).
>>
>> A disadvantage of (1) is that it needs a schema change to add a new
>> type. The advantage of (3) is that it doesn't and it *doesn't* require
>> any extra joins either compared to (1). But I think I'm just failing to
>> communicate that second point so it's over to you from here on...
>> --
>> David Portas
>>
>>
>|||"Zester" <zeze@.nottospam.com> wrote in message
news:uIE29b3OIHA.3532@.TK2MSFTNGP04.phx.gbl...
> Thanks for your input. Hm, that's true that we can just keeping using the
> text description in the main table and just create another table to be
> referenced. So you would have 50 extra tables, but why data integrity is
> an issue when just use check constraint to make sure the set options are
> declared and reinforced? The only drawback I see so far is if we need to
> add new enum value to the set, we need to change the check constraint
> instead of just simply inserting another entry in the permission type
> definition table. However, to do the insertion, we need 50 UI pieces. I
> think there are something I should point out, we host db solution
> internally (on-site, web-based) so modifying the db constraints is an easy
> thing for us comparing with releasing db schema with the solution like
> other product.
>
With a CHECK constraint how would you enumerate the set of values in your
application? If you code them in your app as well then you have to change it
in two different places and rebuild your app just to create a new value. If
you put them in a table your application can easily retrieve them directly
so you don't need to update your app each time you create a new value.
--
David Portas|||That's a good point but I think in general we don't want to make something
dynamic unnecessarily. These enum value sets can be hard-coded in the code.
I believe C# for example can convert enum value to the string (the enum
name) to match with the db. Even if we are dealing with older language, we
can have a layer to do these translations; it's far cheaper than create a UI
to do that for each additional table. Adding a new enum value is not
something should be happening frequently; it breaks switch statement and
if-else combo's very often and create bugs; so the cost of modifying the
contraint is small relative to a total cost of adding a new value because we
always detect it that we need to change it at a small amount of testing.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OXyMip3OIHA.3516@.TK2MSFTNGP02.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:uIE29b3OIHA.3532@.TK2MSFTNGP04.phx.gbl...
>> Thanks for your input. Hm, that's true that we can just keeping using the
>> text description in the main table and just create another table to be
>> referenced. So you would have 50 extra tables, but why data integrity is
>> an issue when just use check constraint to make sure the set options are
>> declared and reinforced? The only drawback I see so far is if we need to
>> add new enum value to the set, we need to change the check constraint
>> instead of just simply inserting another entry in the permission type
>> definition table. However, to do the insertion, we need 50 UI pieces. I
>> think there are something I should point out, we host db solution
>> internally (on-site, web-based) so modifying the db constraints is an
>> easy thing for us comparing with releasing db schema with the solution
>> like other product.
> With a CHECK constraint how would you enumerate the set of values in your
> application? If you code them in your app as well then you have to change
> it in two different places and rebuild your app just to create a new
> value. If you put them in a table your application can easily retrieve
> them directly so you don't need to update your app each time you create a
> new value.
> --
> David Portas
>

Design question regarding comma delimited data

Hi,

I'm trying to figure out if it's good design practice to have several pieces of data in a one column. I explain by example. Let's say you build a movie site. Each movie can belong to several categories. A movie can be Action, Adventure, Fantasy and Drama all at once. Assume a database table with all the movies and another table with all the categories. Now how would I associate one movie with several categories? Would it be OK if I add a Category field in the movie table and then add several categories in that, delimited by commas? Sort of like below:

and so on ...

I can then query the database with a LIKE query if I want to select all movies of a certain category. Personally, I don't like this approach to much, but I can't think of another way to achieve this. Well, there is one other, but I like that one even less. I could create another table that links each movie to a category, but his way each movie with several categories would get a new row. Using the table above, Pirates would get three rows in that table. One with Action, one with Adventure and one with Fantasy. Get my drift?

It all seems counter-intuitive. Thoughts?

Thanks :o)

You should go with the second approach (separate table where you store MovieId, CategoryId) unless you have a good reason to not do it so.

Think about the fact that search by category is not the only possible query you will need. How would you do implement the following using your first approach:Select the number of movies in each category? Using a separate table you can just group by category and do a count().

Keep in mind that normalization is almost always a good thing to do. If you don't know what that means you really have to take a book on database design and read about it.

|||

Create a child table that joins the two tables together

Your Movies Table
MovieID int identity PK
Title varchar
Rating varchar

Your Categories Table
CategoryID int identity PK
Name varchar

Your MovieCategories table
MovieID int
CategoryID int

So if you enter a movie in the Movies table
Title: Pirates of the Carribean
Rating: PG-13

It would get assigned an auto-incrementing ID (in this case 1)

You then populate your Category Table with things like
Action (gets assigned 1)
Adventure (gets assigned 2)
Drama (gets assigned 3)
Comedy (gets assigned 4)
Fantasy (gets assigned 5)

Then in your child table you assign the MovieID for Pirates with the CategoryID's that it belongs to
MovieID CategoryID
1 1
1 2
1 5

Then when you query you would join the necessary tables together to get what you are after.

|||

>>Now how would I associate one movie with several categories?

The method you described isvery typical ( a third table to link the other two).

Table: MovieCategoriesMovieID CatID1 11 32 23 13 23 3

This is the utilimate in flexibility but requires more work from you.

A quick and dirty way (not always bad) would be a boolean column for each category.

This assumes that categories are fixed and you will not be adding new categories

|||

The best will be to create table

Movie with MovieID and Movietittle

Ratings with RatingID and RatingName

Category CategoryID and categoryname

and two tables to link your information

MovieRatings with mrID, mrMovieID, mrRatingID

MOvieCategory with mcID, mcMovieID ,mcCategoryID

With this structure your queries will be much faster when with like statement and also it will save you same space in database

You can have multiple categories connected to movie in movie category table and also different ratings if you need.

Thanks

|||

Thanks for all the replies.

See, I used the, for lack of a better word, "third table" approach before. It's the only way I could think of doing what I wanted it to do. I just never liked that idea of having to create another table. But apparently this is pretty common, so I don't feel so bad about it anymore :o)

It's basically going to look like this:

Movies

movie_id movie_title movie_rating
-------------
1 Bad Movie PG
2 Cool Movie R
3 So So Movie PG-13

Design Question re images

I have a table of people, and for some of them I want to store photo's, is it better to store the phots in a separate table or just add a column to the people table? I'm think about 60% will have photo's.

What is the best way to add a photo to a table?

Hi Graham,

The best practice is to create the image column in a seperate table and have a link column in the primary table.However the design is based on the retrieval of the table data,If your people data is always retrieved with image and no where else it is used in join with other tables it can be part of your main table itself

Regards,

Samsudeen B

|||

First why you want to store those Photos in Database. Consider to strore those on file system and store the File path on your table. It is very cheap to store on the filesystem(memory, retrival, storage & manipulations)..

If you want to store the Photos in database you need to store those in different table as Master Table. You can have those refrences on the detailed table. It is not a bad idea to keep the Image properties on the same (where the photos stored) table, like photo name, photo file ext, photo size etc..

|||Thank you, I had considered storing the filepaths (presumably nvarchar(260) is the best), but I thought the reason image type existed was because it was better to store images within the database, and I thought it might be more secure. But I can easliy fix security.|||

I know lot of people misunderstand with the name. Image is one of the Binary datatype and you can store any binary data like Word Docs, Excel and other binary files (image too). In Sql Server VarBinary(Max) is introduced.

|||

What's the advantage of storing other document types, rather than storing file paths?

Is it a security issue? Surely file permissons can fix that, or is it to do with replication, and distributed databases (not an issue for my needs)?

Tuesday, February 14, 2012

Design For Loading Data Without Knowlged of Datatype or Column Count

I am loading data from an external source into SQL Server via ASP.NET/C#. The problem is that I do not necessarily know the data types of each column coming in, perhaps until a user tells the application, which might not occur until after the data is loaded. Also, I cannot anticipate the number of columns coming in. What would table design look like?

Would you use a large table with enough columns (e.g. Column1, Column2, etc.) reasonable enough to accomodate all the columns that the source might have (32?), and use nchar as the datatype with the plan to convert/cast when I use the data? Isn't the cast kind of expensive?

Does this make sense? Surely other foplks have run into this...

My thanks!

If you're doing what I think you're doing, you might want to use an EAV (Entity/Attribute/Value) table to store your data. The attribute table stores the column definitions. The entity table store the "row id"s. The value table stores the actual values and is of the form

Entity_id int

Attribute_id int

Value (string/whatever)

You can have multiple value tables for different data types, but I find that it's more trouble than it's worth. I've used this on several projects where the data being captured is set by the users at run time.

Thursday, March 29, 2012

Determine Table's PK Columns

Determine table names and column names at runtime?

Tuesday, March 27, 2012

Determine rowguid in column and edit column information ASP.NET 1.1 C#

Determine Late from Time

Sunday, March 25, 2012

Determine if a column is indexed (and the index name)

Thursday, March 22, 2012

determin name of primary column

Wednesday, March 21, 2012

Detecting Carriage Returns in a column

Detect Missing Records in Flat File

Wednesday, March 7, 2012

destination table not exist while adding column in published artic

Designing reusable column transformations?

Saturday, February 25, 2012

designing for both pdf and excel rendering

Designating an identity column when you already have primary key

Friday, February 24, 2012

Design Question: column storing a type

Design Question: column storing a type

Design Question: column storing a type

Design question regarding comma delimited data

Design Question re images

Tuesday, February 14, 2012

Design For Loading Data Without Knowlged of Datatype or Column Count

Determining Security

Blog Archive

About Me