usage of 1 to 1's and many to many's (1 Viewer)

deletedT · Nov 11, 2019

plog said:
1-1 relationships really shouldn't exist--you should accommodate the data in just one table--you wouldn't store first name in one table and last name in another even though they are in a 1-1 relationship. Instead you have 2 fields in 1 table for that data.

I'm not a pro programmer, not even as good as most of members here. But I think you're wrong about 1-1 relationships.
A 1-1 relationship is used to break down a table with too many fields into 2 tables.

In my case, I have a table that takes care of orders to our company. I have several table such as tblParts, tblCustomers, tblMachines, tblProcesses and several more. Another table is used to register the incoming orders and how each order is processed and who has done each process.

It means each order has OrderID, CustomerID, IsToBeDeliveredOn, PartID, Revision, OrderedByID, NeedToBePainted, HasToBeDeliveredOn, ProgrammedByID, ProgrammedOn, WeldedOn, WeldedBy, PaintedOn, PaintedBy......

There's more than a hundred necessary fields for each Order. To keep things simpler, I've divided this table into two tables, One takes care of registered info of the order, one takes care of manufacturing section, each one more than 50 fields. These two tables are linked with a 1-1 relation and the OrderID field as the linked field.

Just curious. How can you take care of this situation with a 1 to many relationship? Or do you keep all the fields in one table?

vba_php · Nov 12, 2019

Tera said:
There's more than a hundred necessary fields for each Order. To keep things simpler, I've divided this table into two tables, One takes care of registered info of the order, one takes care of manufacturing section, each one more than 50 fields. These two tables are linked with a 1-1 relation and the OrderID field as the linked field.

This makes sense, so it's prolly a good way to handle it, although I wouldn't do it myself.

Tera said:
How can you take care of this situation with a 1 to many relationship? Or do you keep all the fields in one table?

In a typical CRM given to a typical business, an orders table would ideally be on the "many" side of a relationship, with the customers table being on the "one" side. But it sounds like your business is fairly complicated if you have parts, welders and other things involved with an order. So I would say if you're knowledgeable enuf bout your business, there's prolly nothing wrong with the way you're doing it.

plog · Nov 12, 2019

Tera, you haven't normalized your data properly. Your use of a 1-1 relationship is the incorrect way to make your data work. Two good rules of thumb you should have used:

1. Table/Field names shouldn't include data. A test to apply is to ask "could any part of this name instead be data within a field instead". [WeldedOn], [PaintedOn], [SolderedOn] - all those verbs prior to "On" should instead be values in a field, not in a name.

2. You shouldn't have sets of fields. A test to apply is to see if your data has "sections" where a certain amount of fields are related. [WeldedOn] & [WeldedBy], [PaintedOn] & [PaintedBy], [SolderedOn] & [SolderedBy] - each section has an On and By. In cases like those it means you need a new table.

All that On and By data needs to go into a much simpler table. This probably reduces the fields you have enormously:

tblProcesses
proc_ID, autonumber, primary key
ID_Order, number, foreign key to Orders table
ID_Process, number, foreign key to Processes table (hopefully this will relate to Painted/Wleded/Soldered)
proc_On, this field will contain whatever data is in all those "On" fields
proc_By, this field will contian whatever data is in all those "By" fields

deletedT · Nov 12, 2019

plog said:
Tera, you haven't normalized your data properly. Your use of a 1-1 relationship is the incorrect way to make your data work. Two good rules of thumb you should have used:

1. Table/Field names shouldn't include data. A test to apply is to ask "could any part of this name instead be data within a field instead". [WeldedOn], [PaintedOn], [SolderedOn] - all those verbs prior to "On" should instead be values in a field, not in a name.

2. You shouldn't have sets of fields. A test to apply is to see if your data has "sections" where a certain amount of fields are related. [WeldedOn] & [WeldedBy], [PaintedOn] & [PaintedBy], [SolderedOn] & [SolderedBy] - each section has an On and By. In cases like those it means you need a new table.

All that On and By data needs to go into a much simpler table. This probably reduces the fields you have enormously:

tblProcesses
proc_ID, autonumber, primary key
ID_Order, number, foreign key to Orders table
ID_Process, number, foreign key to Processes table (hopefully this will relate to Painted/Wleded/Soldered)
proc_On, this field will contain whatever data is in all those "On" fields
proc_By, this field will contian whatever data is in all those "By" fields

Plog,
Thanks for your advice. When I was working on this database, I couldn't find a way to print a report that contains the necessary info as an output.
I'll give it try once again and will be back if any help is needed.

Thanks again.

The_Doc_Man · Nov 12, 2019

I'll also take exception to your comments, Tera, but in a practical way.

You never break down a table into two 1-1 parts. That is useless because if you ever have to rejoin them, you have the same record-size dilemma. But if you break them into THREE parts and only use two at a time, you have gained a size advantage.

If one of the parts is a high-security or high-privacy component, then it would make sense in the OTHER way that we described it, a case where the different partial tables had different treatment requirements. But that is the only time where TWO 1-1 tables make sense.

usage of 1 to 1's and many to many's (1 Viewer)

deletedT

Guest

vba_php

Forum Troll

plog

Banishment Pending

deletedT

Guest

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread