Media Manager Schema

Project: SqlServer

Layout: Media Manager

 

Tip: Click on a table to jump to the field-level descriptions for the table.

 

 

Attachment

Id

int NOT NULL AUTOINCREMENT

FileName

nvarchar( 255 ) NOT NULL

DisplayName

nvarchar( 255 )

Indexes

PK_MEDIAMANAGER_ATTACHMENT_ID primary key

ON Id

 

Audit

AuditId

bigint NOT NULL AUTOINCREMENT

AuditTableId

int NOT NULL

TableRecordPrimaryKeyID

nvarchar( 128 ) NOT NULL

ActivityType

char( 1 ) NOT NULL

ChangeDescription

xml NOT NULL

AuditDate

datetime DEFO getdate()

AuditUserNameHash

binary( 20 )

Indexes

PK_MediaManager_Audit primary key

ON AuditId

IX_Audit_Created_LastMod

ON AuditTableId, TableRecordPrimaryKeyID, AuditId

Foreign Keys

FK_MediaManager_Audit_AuditTable

( AuditTableId ) ref AuditTable (AuditTableId)

 

AuditTable

AuditTableId

int NOT NULL AUTOINCREMENT

TableSchema

nvarchar( 128 ) NOT NULL

TableName

nvarchar( 128 ) NOT NULL

Indexes

PK_MediaManager_AuditTable primary key

ON AuditTableId

 

Contact

Id

int NOT NULL AUTOINCREMENT

Name

nvarchar( 128 ) NOT NULL

StreetAddress

varchar( 512 )

City

varchar( 128 )

State

varchar( 64 )

ZipCode

varchar( 16 )

EmailAddress

nvarchar( 256 )

TelephoneNumber

nvarchar( 50 )

Indexes

PK_MEDIAMANAGER_CONTACT_ID primary key

ON Id

UQ__Contact__737584F635BCFE0A unique

ON Name

 

DataLocation

Id

int NOT NULL AUTOINCREMENT

CaseProductEnvironmentId

int

MediaId

int NOT NULL

CustodianId

int

DataLocationJobId

int

Custodian

nvarchar( 256 )

MediaPathId

bigint NOT NULL

SourceTypeId

int

Case

varchar( 128 )

Client

nvarchar( 256 )

Indexes

PK_MEDIAMANAGER_DATA_LOCATION_ID primary key

ON Id

Foreign Keys

FK_MediaManagerDataLocation_ToMediaManagerDataLocationJob

( DataLocationJobId ) ref DataLocationJob (Id)

FK_MediaManagerDataLocation_ToMediaManagerMedia

( MediaId ) ref Media (Id)

FK_MediaManagerDataLocation_ToMediaManagerMediaPath

( MediaPathId ) ref MediaPath (Id)

 

DataLocationJob

Id

int NOT NULL AUTOINCREMENT

MediaId

int NOT NULL

EnterpriseJobId

int

GlobalJobId

uniqueidentifier

ProductId

int

DiscoveryJobId

int

DirectoryCount

int

TotalFileCount

int

TotalFileSize

bigint

DirectoriesExpectedCount

int

DirectoriesCopiedCount

int

FilesExpectedCount

int

FilesCopiedCount

int

FilesExpectedSize

bigint

FilesCopiedSize

bigint

CopyWasSuccessful

bit

CopyErrors

nvarchar(max)

OriginalFileCount

int

FilesCompletedDiscoveryCount

int

FilesCompletedExportCount

int

FilesErroredCount

int

FilteredCount

int

DuplicateCount

int

Status

nvarchar( 32 )

Progress

nvarchar( 32 )

CreatedBy

nvarchar( 256 )

CreatedDate

datetimeoffset( 34 )

JobType

varchar( 128 )

CopyStatus

nvarchar( 32 )

CopyErrorCount

int

DiscoveryStatus

nvarchar( 32 )

DiscoveryErrorCount

int

ExportStatus

nvarchar( 32 )

ExportErrorCount

int

LoadedStatus

nvarchar( 32 )

LoadedErrorCount

int

LoadedCount

int

DiscoveryNodeLevelErrorCount

int

CompletedDate

datetimeoffset( 34 )

Indexes

PK_DATALOCATIONJOB_ID primary key

ON Id

Foreign Keys

FK_DataLocationJob_ToEnterpriseProduct

( ProductId ) ref Product (ProductId)

FK_DataLocationJob_ToMedia

( MediaId ) ref Media (Id)

 

Delivery

Id

int NOT NULL AUTOINCREMENT

SenderId

int

DeliveryType

varchar( 64 ) NOT NULL

DeliveryName

nvarchar( 128 )

Method

nvarchar( 64 ) NOT NULL

Carrier

nvarchar( 64 )

Shipped

datetimeoffset( 34 )

Arrived

datetimeoffset( 34 )

TrackingNumber

nvarchar( 64 )

ExternalId

nvarchar( 128 )

Notes

nvarchar( 512 )

Indexes

PK_MEDIAMANGER_DELIVERY_ID primary key

ON Id

Foreign Keys

FK_MediaManagerDelivery_ToMediaManagerContact

( SenderId ) ref Contact (Id)

 

Delivery_Media

MediaId

int NOT NULL

DeliveryId

int NOT NULL

Foreign Keys

FK_Delivery_Media_DeliveryId

( DeliveryId ) ref Delivery (Id)

FK_Delivery_Media_MediaId

( MediaId ) ref Media (Id)

 

FileExtensionSummary

Id

int NOT NULL AUTOINCREMENT

DataLocationJobId

int NOT NULL

MediaId

int NOT NULL

FileExtension

nvarchar( 100 ) NOT NULL

FileCount

int NOT NULL

TotalFileSize

bigint NOT NULL

HiddenCount

int NOT NULL

ReadOnlyCount

int NOT NULL

ArchiveCount

int NOT NULL

Indexes

PK_MEDIAMANAGER_FILEXTENSIONSUMMARY_ID primary key

ON Id

Foreign Keys

FK_MediaManagerFileExtensionSummary_MediaManagerDataLocationJob

( DataLocationJobId ) ref DataLocationJob (Id)

FK_MediaManagerFileExtensionSummary_MediaManagerMedia

( MediaId ) ref Media (Id)

 

FileUpload

Id

int NOT NULL AUTOINCREMENT

MediaId

int

FileName

nvarchar( 500 ) NOT NULL

FileSize

bigint

Description

nvarchar( 256 )

SourceTypeId

int

CaseProductEnvironmentId

int

CaseName

nvarchar( 100 )

CustodianId

int

Custodian

nvarchar( 256 )

ClientId

int

ClientName

nvarchar( 256 )

UploadDateStart

datetimeoffset( 34 )

UploadDateEnd

datetimeoffset( 34 )

LastModifiedDate

datetimeoffset( 34 )

UploadStatusDescription

varchar( 50 )

UploadStatusPercentage

decimal( 18, 2 )

CreatedBy

nvarchar( 256 )

ChunkStatus

varchar(max)

TargetUploadNetworkPath

nvarchar( 2000 )

ChunkSize

int

RowVersion

timestamp NOT NULL

Indexes

PK_MEDIAMANAGER_FILEUPLOAD_ID primary key

ON Id

Foreign Keys

FK_MediaManagerFileUpload_EnterpriseCaseProductEnvironment

( CaseProductEnvironmentId ) ref CaseProductEnvironment (CaseProductEnvironmentId)

FK_MediaManagerFileUpload_EnterpriseClient

( ClientId ) ref Client (ClientId)

FK_MediaManagerFileUpload_MediaManagerMedia

( MediaId ) ref Media (Id)

FK_MediaManagerFileUpload_MediaManagerSourceType

( SourceTypeId ) ref SourceType (Id)

 

Media

Id

int NOT NULL AUTOINCREMENT

Barcode

varchar( 128 )

MediaType

nvarchar( 64 ) NOT NULL

OriginalMedia

bit NOT NULL DEFO 1

Description

nvarchar( 512 )

Size

decimal( 18, 3 )

SizeUnit

varchar( 32 )

SerialNumber

nvarchar( 512 )

ContainerType

varchar( 32 )

DriveType

varchar( 32 )

DriveConnection

varchar( 32 )

DriveFormFactor

varchar( 32 )

DriveModel

nvarchar( 512 )

Label

varchar( 128 )

Manufacturer

varchar( 128 )

NetworkPath

varchar( 2083 )

MediaHash

varchar( 128 )

DirectoryCount

int

TotalFileCount

int

TotalFileSize

bigint

ExternalId

varchar( 128 )

ClientId

int

StorageLocationId

int

Indexes

PK_MEDIAMANAGER_MEDIA_ID primary key

ON Id

Foreign Keys

FK_MediaManagerMedia_EnterpriseClient

( ClientId ) ref Client (ClientId)

FK_MediaManagerMedia_MediaManagerStorageLocation

( StorageLocationId ) ref StorageLocation (Id)

 

MediaAttachment

Id

int NOT NULL AUTOINCREMENT

MediaId

int NOT NULL

AttachmentId

int NOT NULL

IsImage

bit

IsDefault

bit

Indexes

PK_MediaAttachment_ID primary key

ON Id

Foreign Keys

FK_MediaAttachment_Attachment

( AttachmentId ) ref Attachment (Id)

FK_MediaAttachment_Media

( MediaId ) ref Media (Id)

 

MediaBarcodeSetting

Id

int NOT NULL AUTOINCREMENT

Prefix

varchar( 32 ) NOT NULL DEFO ''

Padding

int NOT NULL DEFO 12

Indexes

PK_MediaBarcodeSetting primary key

ON Id

 

MediaPath

Id

bigint NOT NULL AUTOINCREMENT

Path

nvarchar( 450 ) NOT NULL

ParentId

bigint

Title

nvarchar( 255 ) NOT NULL

IsDirectory

bit NOT NULL DEFO 1

MediaId

int NOT NULL

DataLocationState

int NOT NULL DEFO 0

ItemType

nvarchar( 50 )

Indexes

PK_MEDIAMANAGER_MEDIAPATH_ID primary key

ON Id

IX_MediaPath_Parent

ON ParentId

IX_MediaPath_Path

ON Path

Foreign Keys

FK_MediaManagerMediaPath_MediaManagerMedia

( MediaId ) ref Media (Id)

FK_MediaManagerMediaPath_MediaManagerMediaPath

( ParentId ) ref MediaPath (Id)

 

MediaPathScanStatus

Id

int NOT NULL AUTOINCREMENT

MediaId

int NOT NULL

MediaPathId

bigint

ResultId

bigint

LastModifiedDatetime

datetime

LastModifiedBy

varchar( 256 )

ScanStatus

varchar( 20 )

ErrorMessage

varchar(max)

Indexes

PK__MediaPat__3214EC075D95E53A primary key

ON Id

Foreign Keys

FK_MediaManagerMediaPathScanStatus_MediaManagerMedia

( MediaId ) ref Media (Id)

FK_MediaManagerMediaPathScanStatus_MediaManagerMediaPath_MediaPathId

( MediaPathId ) ref MediaPath (Id)

FK_MediaManagerMediaPathScanStatus_MediaManagerMediaPath_ResultId

( ResultId ) ref MediaPath (Id)

 

MediaStates

Id

int NOT NULL AUTOINCREMENT

MediaId

int NOT NULL

StateId

int NOT NULL

LastModifiedDateTime

datetimeoffset( 34 ) NOT NULL

Indexes

PK_MediaStates primary key

ON Id

Foreign Keys

FK_MediaManagerMediaStates_ToMedia

( MediaId ) ref Media (Id)

FK_MediaManagerMediaStates_ToState

( StateId ) ref States (Id)

 

PickList

Id

int NOT NULL AUTOINCREMENT

Name

nvarchar( 100 ) NOT NULL

DisplayName

nvarchar( 200 ) NOT NULL

Indexes

PK__PickList__3214EC073D5E1FD2 primary key

ON Id

 

PickListItem

Id

int NOT NULL AUTOINCREMENT

PickListId

int NOT NULL

Value

nvarchar( 100 ) NOT NULL

DisplayName

nvarchar( 200 ) NOT NULL

Position

int NOT NULL

Indexes

PK__PickList__3214EC0773BA3083 primary key

ON Id

Foreign Keys

FK_MediaManagerPickListItem_ToMediaManagerPickList

( PickListId ) ref PickList (Id)

 

SourceType

Id

int NOT NULL

Name

nvarchar( 64 ) NOT NULL

Indexes

PK_SOURCE_TYPE_ID primary key

ON Id

 

States

Id

int NOT NULL

Name

nvarchar( 64 ) NOT NULL

Indexes

PK__States__3214EC074316F928 primary key

ON Id

 

StorageLocation

Id

int NOT NULL AUTOINCREMENT

Name

nvarchar( 255 )

Indexes

PK_MEDIAMANAGER_STORAGELOCATION_ID primary key

ON Id

 

View vAuditCreatedByModifiedBy

________________

Enterprise 2017.4.0