Having a 1 big table vs 3 related tables

[teachme]

Hi, I am building a project status database where I have 3 tables. 1 for basic project info. second is about timelines and 3rd is about dependencies. I have close to about 100 fields. I am having a hard time creating relationships. can I not put all fields in 1 table?

What are some of the disadvantages of having them all in 1 table?

This will be a multi user database. approx 5 people using at the same time - delay issues?

Thanks

 

[Ranman256]

If you have 100 fields,the table may be designed wrong.
You may want to design it vertically, rather than horizontally w 100 fields.

And 1 table would have repeated data as opposed to 3 tbls.

 

[teachme]

If you have 100 fields,the table may be designed wrong.
You may want to design it vertically, rather than horizontally w 100 fields.

And 1 table would have repeated data as opposed to 3 tbls.

Not sure if I will have repeated data. Each field will have a text stored or it will either be blank. Am i missing something? thx

 

[MarkK]

A database system is a model of a real-world system, and you need to design your tables so that they form an accurate model of the problem you are trying to solve. If you fail to create an accurate model, your data system will never fulfill your expectations.

In a common business problem, one Customer might have many Orders, and in that case there is a one-to-many relationship between the Customer table and the Order table. In this case there is one correct model: two tables, with a one-to-many relationship, one customer to many orders.

100 fields in one table is almost certainly a design flaw.

Check out this article for more info. https://en.wikipedia.org/wiki/Entity–relationship_model

 

[The_Doc_Man]

In a sense you have answered your own question by revealing that your database tracks three different kinds of data - projects, schedules, and dependencies. One table is DECIDEDLY not the way to go. They each deserve their own separate table.

I suspect the "dependencies" part is what is hurting your head the worst in this design. Schedule dependencies are based on a table that makes TWO references to another table. In Access, you can do this by having TWO foreign keys to the project table, one a "predecessor" link and one a "successor" link. You need to have the project table appear TWICE in whatever diagram you are making. (No, not two project tables... two REFERENCES to the single project table.) When drawing out the relationships, you add the project to the relationship workspace twice, then add this relationships table, then link one of the keys to the first instance of the project table and the other to the second instance, which will probably look like PrjTable(1) or maybe (2). I have to admit it has been a while since I did one of these.

The trick is then to write some code that traverses the relationship tables by gathering them according to their predecessor. To make this kind of thing work out, you might need to create two "fake" project entries - a "start" milestone and a "finished" milestone - for which your relationships have the start milestone as their predecessor but NO relationships have the start milestone as the successor.

By the way, I hope you are not using 'relationship' as a field name or table name because it is dangerously close to a keyword. Access doesn't like it when you use keywords as names of other objects.

Your schedule would of course be derived by taking the length of each project step along the path defined by the relationships. It is acceptable to have multiple predecessors and multiple successors to a step, and it is acceptable to have a step that is of zero duration to act as a rendezvous point or dispatch point (many relationships in or many relationships out).

The only thing that makes me pause here is, if you have Office already, there is an Office product called Schedule that does this for you. Did someone think this would be cheaper to build than to buy? Or is this just a school project?

 

[teachme]

I greatly appreciate ya'all for the responses. I think I need to clarify a few things. I am building this database just for the purpose of capturing the project related data for reporting - thats it. I will have a few queries and a few reports based on the fields on a couple of forms.

The biggest thing for me is the muti-user compatibility.

I understand that all of you are recommending separate related tables but I am looking for specific technical reasons why a single table is not a good option for what I am trying to do here (i am asking for my learning only). After all, you can have up to 255 fields in a table in Access.

Why do i have to worry about relationships if I can collect all data without any related tables?

I looked online too but couldnt find any specific technical reasons - thanks in advance

 

[Ranman256]

if you need 255 fields or more, the table is designed wrong.

 

[teachme]

if you need 255 fields or more, the table is designed wrong.

I dont need 255 fields. I was saying that this is the max fields allowed in a table. Thx

 

[gemma-the-husky]

it's what MarkK says.

it's a bit of a tautology, but the correct number of tables is the number of tables resulting from data analysis and normalisation

unlikely, but you may even have a table with more than 255 fields - in which case this is one of the rare occasions when a 1-1 join is necessary.

 

[teachme]

OK - I see why multiple tables is recommended thx

 

[The_Doc_Man]

Why do i have to worry about relationships if I can collect all data without any related tables?

It ain't what you collect, it is the way you store it for subsequent analysis that governs how many tables are involved in what you are doing. However, I'll be honest - you really COULD use a single table for this. Access doesn't really care about whether your tables are normalized or not for simple search queries. It is just that certain Access abilities (having to do with reports and group headers and indexing for faster searches) presume that you have tried to normalize your data.

 

[teachme]

It ain't what you collect, it is the way you store it for subsequent analysis that governs how many tables are involved in what you are doing. However, I'll be honest - you really COULD use a single table for this. Access doesn't really care about whether your tables are normalized or not for simple search queries. It is just that certain Access abilities (having to do with reports and group headers and indexing for faster searches) presume that you have tried to normalize your data.

Yes, I see your point.

I was in fact able to build the relationships and i am now using three tables.

Thx