Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mallethead
Starting Member
2 Posts |
 Posted - 2011-10-03 : 12:56:22
|
| Hello,I have a requirement to construct an application that is very different from others I've done. I thought I'd put this out to see if someone can give me a suggestion on how to structure the tables. This is an HTML report that will be viewed in the web-browser or printed by users for reference. I am also doing the form to enter the data.Basically,.......I have1. jobnumber2. jobnumber has an format type3. that format type has from 5 to 15 required componentsSo, when the user enters a jobnumber they will select a "format type" and only the required components necessary for that format type should be displayed.The required components are a limited set such as: text, picture, logo, disclaimer, etc. and different formats will have various combinations of those components.I think I need:1. a table of jobs2. a table of formats (already exists)3. a table of required components4. a table of the components for a particular format typeThis 4th table would be like:job: 1432format: movierequired components for movie: 1,2,4 (for text, picture disclaimer)Etc.In the end I have to show each job listed and then only the required components for that particular type of job and their particular data such as received, comlete, OK'd, etc.Thanks in advance to anyone who has an idea. Possibly it's not easy to represent the structure in words so I'll get a data modelling tool going.Thanks, Mallethead |
|
|
Rich_z7
Starting Member
5 Posts |
Posted - 2011-10-11 : 07:35:21
|
| Hi,I wouldn't be thinking about one table for this, but quite a few (15....)Your main table would contain the Job details such as teh Job name, description, format type, owner ??, creation date etc etc etc, and finally an incrementing NUMC field which is populated when the record is inserted in the database.The NUMC field would then be used as a Primary key linking the text, picture, logo, disclaimer tables to the main job. Entries would only be created in these subsidiary tables if the format type requires them.RegardsRich |
 |
|
|
mallethead
Starting Member
2 Posts |
Posted - 2011-10-11 : 09:08:27
|
| Rich_z7,Thanks for the input. It's been a bit since I originally posted this and came up with a plan. I ended up with 4 tables as I didn't want to split all the individual categories up into their own tables.I'm using a cross-reference table to link one activity to many components and then storing the data for each projects components in a separate table using foreign keys to identify project number and component name.Seems to be working well.Thanks, Jerry |
|
|
|
|
|
|
|
|
suggestions for table structure