The Database

I’m using SQL server but, as you know, MVC is remarkably agnostic to most database flavours.

Here’s my Photo table. I didn’t call it images because there are so many other objects with that name your compiler might get confused about.

SQL

CREATE TABLE [dbo].[Photo] (

    [Id]              INT              IDENTITY (1, 1) NOT NULL,

    [Creator]         NVARCHAR (256)   NULL,

    [SourceFilename]  VARCHAR (128)    NULL,

    [Description]     NVARCHAR (4000)  NOT NULL,

    [Keywords]        NVARCHAR (400)   NULL,

    [AspNetUserID]    VARCHAR (128)    NULL,

    [ForeignKeyTable] TINYINT          NULL,

    [DateCreated]     SMALLDATETIME    DEFAULT (getdate()) NOT NULL,

    [DateSaved]       SMALLDATETIME    DEFAULT (getdate()) NOT NULL,

    [Flag]            BIT              DEFAULT ((0)) NOT NULL,

    [Count]           INT              DEFAULT ((0)) NOT NULL,

    [GUID]            UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,

    CONSTRAINT [PK_dbo.Photo] PRIMARY KEY CLUSTERED ([Id] ASC)

);

Most of the fields are self-explanatory

  • Id is the identity field, generated by SQL server when a new record is made.
  • Creator is the name of the photographer – or the name of whoever uploaded the file
  • Description is the only required field it is used to describe the subject of the photo
  • Keywords are words additional to those in the description that a user might search on
  • AspNetUserID is the user id (a GUID) in the AspNetUsers table with a one (user) to many (photos) relationship
  • ForeignKeyTable is reserved for future use
  • DateCreated generated by SQL server when a new record is made.
  • DateSaved generated by SQL server when a new record is made and updated whenever the record is saved.
  • Flag automatically set to false by SQL server when a new record is made. Allows you to hide a record later by setting it to true.
  • Count and int field you can increment e.g. when someone votes for a photo in a contest or views it in a library
  • GUID generated by SQL server when a new record is made. This is used to create an obfuscated filename

The relevance of some of the above fields may not be apparent yet but, be patient, they will be later!

Next >>>