Help needed in Database Design

[dan_man]

Hi All,

I am trying to create a database to store Broadcastig transmitters and sites information:banghead:.
So far I have tried all possible methods I am aware of to design the same all these without any noticeable result.

There are 4 regional centers (DR) each of them managing a number of so called CIZ (Zonal Intervention Center). Each CIZ are managing a number of unmanned sites. There were also sites with personnel not belonging to any CIZ but to the regional centre (DR)

Also a CIZ can be located at a transmitter site or not.

On each site could be one or more equipments:
1. Tv Transmitter
2. FM transmitter
3. AM transmitter
4. Radio Relay link equipment
5. Communication equipment
6. One or more poles or towers for antennas
7. Power generator (for power outages)
8. Backup battery etc

On each pole or tower can be one or more antennas for different transmitters (TV, FM, AM, Radio Relay). On sites with more than one pole or tower I can have also one antenna on multiple towers.

So far I’ve created some tables:

tblSites, tblTvEquip, tblFMEquip, tblPole, tblTvFMAntenna

and a form to select a site in a cascade combo

frmDRuri

Here is what I need in the database.

1. Each Site can have one or more transmitters;
2. Each Tv equipment is either a Transmitter or a Transposer. The main difference is a transmitter use a signal source from satellite or Radio Relay Link while a transposer is using as a signal source another transmitter called „mother”
3. On a site form I would like to have each kind of equipment (Tv, FM, AM Radio Relay, Communication, Tower, Power Generator, Backup Battery, etc) on separate tabs. If an equipment type is not present at the site I would like to have his corresponding tab invisible or at least grayed.
4. On Tv tab I would like to have the information related to transposer also invisible if the equipment is defined as transmitter.
5. Beside its obvious scope (inventory of the used equipments in all sites) I would also want to use this database for tracking technical failures per transmitting equipment and also for tracking all expenditures related to each site or equipment.


Please let me know if this is possible. If anyone can help me. There are further more issues with what I have explained here. Thinking of going step by step. Any help on this is greatly appreciated.

 

[plog]

First, forget about forms and tabs and what font to use for labels on sub-forms. Concentrate on your table's structure. They're will be enough time to choose a theme and color for the upstairs bathroom after you make sure the basement foundation is set properly.

With that said, I think you need to look at normalization (http://en.wikipedia.org/wiki/Normalization). I didn't dive into your data too deeply, but I saw three major issues:

1. Circular Reference. Your relationship looks like a spider web. Generally there should be only one way from one peice of data to another. For example, tblSite is indirectly linked to tblTvFMAntenna in 3 ways (via tblTower, tblFMEquip and tblTVEquip). That is incorrect. I didn't dig too deep into your explanation or your structure, but I am certain that is not how your tables should relate.

2. An issue that is probably related to #1 above is that I see no Junction tables (http://en.wikipedia.org/wiki/Junction_table). Those tables define many-many relationships. From your explanation it sounds like you have a few, however you have no Junction tables in your database to define those many-many relationships.

3. This is isn't as a major an issue as the prior 2, but you have numerated fields (Pannels_1, Pannels_2, Azimuth_1, etc.) in tblTvFMAntenna. Those are always a sign of an improperly structured database. Instead of adding fields to a table, you should add another table and store that data there in multiple rows instead of multiple columns.

 

[Simon_MT]

To me this is a case of isolating the top and the bottom.

DR > CIZ > SITES

EQUIP > POLE > SITES

In other words from an engineering / tranmission point of view it is the equipment in situ that is of primary importance whilst how it all fits into an organisation framework to an engineer is rather incidental.

Simon

Simon

 

[dan_man]

1. Circular Reference. Your relationship looks like a spider web. Generally there should be only one way from one peice of data to another. For example, tblSite is indirectly linked to tblTvFMAntenna in 3 ways (via tblTower, tblFMEquip and tblTVEquip). That is incorrect. I didn't dig too deep into your explanation or your structure, but I am certain that is not how your tables should relate.

You're right, I will try to reconfigure the relationships between tables and I will come back with a new srtucture. Do you have any sugestion?

2. An issue that is probably related to #1 above is that I see no Junction tables. Those tables define many-many relationships. From your explanation it sounds like you have a few, however you have no Junction tables in your database to define those many-many relationships.

I am thinking to define a junction table with Equipment related to each Site. I have to find a way of linking different equipment description with this table

3. This is isn't as a major an issue as the prior 2, but you have numerated fields (Pannels_1, Pannels_2, Azimuth_1, etc.) in tblTvFMAntenna. Those are always a sign of an improperly structured database. Instead of adding fields to a table, you should add another table and store that data there in multiple rows instead of multiple columns.

Usually an antenna have one to four main radiation directions. There is no rule regarding number of azimuths or number of pannels on each direction. The antenna radiation pattern is strictly related to the area where it is installed. Each antenna is, from this point of view, unique. This is why I choose this kind of description. I am not pretending that this is the best description but is the only one I've found. If you have any proposal I am open to sugestions.

I am just an maintenance engineer who is trying to make his life easier with this database. So any suggestions from you as access professionals are more than welcome.

Thank you,

Dan

 

[dan_man]

Yes Simon, you're right. The main issue here is the equipment in situ. Unfortunatelly I do not know yet how to solve this issue. As I said in my previous post I am open to any sugestions in order to make this database working.

 

[plog]

My suggestion would be to read up on normalization. It sounds like you have a good grasp of what your data is and the relationships, you just need to read up on how to create and establish them using a database.

 

[dan_man]

Hi,

I just redesigned the database relationships. I've eliminated circular references and I added some more tables and fields. Please advise

Best Regards,

Dan

 

[plog]

Without knowing your system and data that well, I would say it mostly looks fine. You still have numerated field names (Azimuth_1, Azimuth_2, etc.) which means you should have a seperate table for that data. And I am unsure of all the Yes/No check boxes in tblSites--those two might need to go into their own table.