Beginners Design Help

[Jagstrom]

Trying to put together my 1st DB.

The objective is to see reports showing differences between Satellite television providers, to answer questions like what channels does one gain or lose when switching providers and or channel packages. I also am going to include a lot of supporting data like Logos(attached files),channel numbers, cost etc..

I'm stuck on these four related fields; (Examples):

1. ChannelName (Sci-Fi Channel)
2. Provider (Dish, or DirecTV)
3. Resolution (STD, or HD)
4. ChannelNumber (122 on Dish,or 262 on DirecTV)

ChannelNames seems like a One-to-Many with each of the other three fields, but the Many side depends on the others as well.

If my description confuses you as much as me, then please just think of the real world relationships and suggest how I should set these Table/Field's up and how to define the relationships.

Thanks in advance

 

[PHV]

A starting point (PK=PrimaryKey, FK=ForeignKey)
tblChannels
ChannelID AutoNum,PK
ChannelName

tblProviders
ProviderID AutoNum,PK
ProviderName

tblChanProv
ChanProvID AutoNum,PK
ChannelID Long,FK
ProviderID Long,FK
ChannelNumber
Resolution

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

Your 1st db. Have you taken any Access courses? If not, good luck.
Normalization is extremely important in creating a table. Then you must understand relationships. Notice what you thought was a one-to-many is actually a many-to-many which relational database don't like. PHV introduced you to a junction table. See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[Jagstrom]

thanks,

Its looking better now. I failed to mention a 5th field, that is ChannelPackages.

Each Package is from one of many Providers,
Containes channelNames,ChannelNunmbers, and Resolutions
ChannelName is independant of Provider
ChannelNumber, and Resolution are dependant upon Provider.

How would I relate ChannelPackages?

 

[PHV]

Replace this:
tblChanProv
ChanProvID AutoNum,PK
ChannelID Long,FK
ProviderID Long,FK
ChannelNumber
Resolution

with this:
tblChannelPackages
ChannelPackagesID AutoNum,PK
ChannelID Long,FK
ProviderID Long,FK
ChannelNumber
Resolution

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Jagstrom]

Thanks for the quick reply, I've ran outa time for now, I'll look into it a bit later