|
[QUOTE=mikesouth])
Aliases Table
puid INTEGER
alias VARCHAR puid+alias = Primary Key
------------------------------------------------------
this kind of table doesn't need a combination primary key. you can just use an auto-increment field called like AID (integer), with an index on puid and an index on alias.
----------------------------------------------------
Shoots Table
Shootid INTEGER Primary Key
ShootDate DATE (Date of Shoot)
Location VARCHAR (Shoot Location)
Video CHAR(1) (Video Y/N)
Photos CHAR(1) (Photos Y/N)
-----------------------------------------------------
probably should also include a production/shoot title, ie. the movie name, the setting of the shoot (ie. barbie on the beach with red bikini) since a shoot could be a movie or a photo spread. The other reason for having a title, is if you file for copyright, so that the work (shoot) is grouped
--------------------------------------------------------
ShootPerformers Table
shootid INTEGER
puid INTEGER shootid + puid = Primary Key
--------------------------------------
primary key should just be an auto-incremented field like name SPID (integer)
with index on shootid and index on puid
----------------------------------------
Website Table
Wid INTEGER Primary Key
WebsiteName VARCHAR (Site Name)
shootid INTEGER
url VARCHAR (URL of the photos or video in this shoot)
startfilename VARCHAR (the filename of the first file in the sequence of files that make up this shoot)
endfilename VARCHAR (The filename of the last file in the sequence)
----------------------------
since there can be multiple URL to a website table entry, would need a new table that is like WebsiteURL where you can list the multiple URLs
---------------------------
Movies Table
Mid INTEGER Primary Key
Mname VARCHAR (movie name)
Shootid INTEGER
SceneNo SMALLINT (Scene number that this shoot appears in)
i think you have it.. i would have to draw the schema on the whiteboard to make sure all the keys lined up for doing queries, but it looks good with the above comments.
i built a 2257 database a year ago,polled some content producers, they didn't have any interest in paying for a database system to organize their records. wasn't worth it, so the project got shelved.
using MySQL, FileMaker, Access, even Excel, one can organize their records to be searchable by the required fields (the 2257 regs are pretty specific on the specs for this).
There are other things to do to comply with 2257 beyond the indexing piece that Mike has presented... but its a great start to atleast doing good recordkeeping.. which is what 2257 is about (and about shutting down adult sites).
Fight the 1st Normal Form!
|