Tables, Strucuture, Multiple Inputs (1 Viewer)

[shades]

Howdy. I have a question on design of tables. Most of the databases I have seen developed here (and in books) assume that there is some kind of individual input of data. Thus, one major table for input (of various sources) worked well on my other projects. This one doesn't seem to fit the mold.

I have Excel/CSV files from five different vendors. Each deals with a different kind of data. That is, while all of them will have a few common elements, there are several significant differences in the other fields that I cannot bring them into the same table. For instance, in one table one field might be "Size" and refer to inches, while another table would have size, but it refers to the portion of the page (1/4, 1/3, etc.). Thus each of the five vendor tables have unique fields.

Tables:
Input tables:

tblTV
tblNPP
tblRAD
tblOOH
tblONL

Ref Tables:

tblSEGMENT
tblMEDIATYPE
tblDMA
tblDAYPART

For instance, fields for tblTV

TVID
Station
DMA
Daypart
Program
Length
DateStart
DateEnd
TRP
NumberSpots
Cost
MediaType
Cons/Bus
CreativeDescript
CreativeOffer
Segment

So, my questions:

1. Is it acceptable to have five input tables, rather than one?

2. These five vendors will update their input data on a daily weekly basis. This, there will be changes to some fields and addition of many new records. What is the best way to handle this?

3. In addition, we will have 2-5 people updating several fields (i.e. last five in tblTV above) that the vendors cannot supply. Will forms be the easiest way to accomplish these updates?

3. Relationships will be critical in getting this set up correctly. Any suggestions?

Any kind of direction, suggestions will be very much appreciated.
________
SUZUKI DR-Z400 SPECIFICATIONS

 

[ScottGem]

I would need to know more about the the app does and how its going to be used. One way to deal with the issue of unique fields is to use additional tables in a one-to-one relation for the unique data.

 

[shades]

Howdy, Scott. The goal is to provide a report (in XL spreadsheet) of up-to-date media runs top check for overlap and coordination. So queries would have to allow pulls from all five tables and from as many as 100 DMAs (markets) or selected DMAs, as well as specific dates or specific time-periods. The report would be a type of flow chart (which I can do in XL after the cooridnation is completed in Access). The time period could be two weeks, one month or any time period above that up to six months.

Also, to note that some media have DateStart and DateEnd but others have DateInsert, and these are not related.
________
Smoking Kills

 

[ScottGem]

Ok, reading between the lines, these are like advertising spots, in various types of media (TV, radio, print)? If so, I would have ONE table for each spot with an identification of the media type. Where you have data that is totally unique to a media type you can have separate tables in a one ot one relation. But I would put as much in the one table as normalization allows. For example, you could have a size field in your main table, but the unit would depend on the media type. For example broadcast media would measure the amount in seconds, while print media would measure in a fraction of a page size. If the measurement varies by outlet (ie. One newspaper charges by column inch while another charges by fraction of a page), you can store the measurement in the table.

A similar thing would be used for your Dates. I would assume that Date start would be for broadcast media where you buy for a period of time. Whereas, with print you might insert in a specific issue. But DateStart and DateInsert are essentially the same. its the media type that will determine how its used.

 

[shades]

Thanks, Scott.

Ok, reading between the lines, these are like advertising spots, in various types of media (TV, radio, print)?

Yes, exactly.

If so, I would have ONE table for each spot with an identification of the media type. Where you have data that is totally unique to a media type you can have separate tables in a one ot one relation. But I would put as much in the one table as normalization allows. For example, you could have a size field in your main table, but the unit would depend on the media type. For example broadcast media would measure the amount in seconds, while print media would measure in a fraction of a page size. If the measurement varies by outlet (ie. One newspaper charges by column inch while another charges by fraction of a page), you can store the measurement in the table.

I guess this where I am stuck in my thinking. While the example of the TV table in my OP has the total data needed. Only a portion of that would come from the TV vendor:

TVID
Station
DMA
Daypart
Program
Length
DateStart
DateEnd
TRP
NumberSpots
Cost

- but we are talking as many as 100 markets with many TV stations in each market. Each updated weekly. So, are you saying that this (TV table) would be an import table, and then pull this data via query into a main table? Then, there would be five import tables, but one where all is consolidated called the main table? And I would delete the data in the Import tables after it is imported into the main table? This is what I had in mind originally.

A similar thing would be used for your Dates. I would assume that Date start would be for broadcast media where you buy for a period of time. Whereas, with print you might insert in a specific issue. But DateStart and DateInsert are essentially the same. its the media type that will determine how its used.

Yes, the dates would be that way.

I really appreciate your comments and suggestions.
________
Montana Medical Marijuana Dispensaries

 

[ScottGem]

You could input from the vendor files either by linking to files they provide (recommended) or by importing into a temp file. You would then run a series of Update queries, that would take the data to append to your actual tables. One key to this is the vendor supplying you with a key field to identify each record.

 

[shades]

That was my next question.

Thank you very much. As I get the go-ahead on this project (very soon!), I'm sure I will have additional questions but this helps tremendously.
________
Z500/Z550

 

[ScottGem]

Glad to assist

 

[shades]

The Good News and ...

The Good News = They have decided not to put the project into Access (by the time it was developed, the crisis/peak demand would have past)

The Better News = I have a head start in case "they" change their minds.

Thanks, Scott.
________
No2 vaporizer