You are not logged in. Please login or register.
My Visual Database → General → Criteria for choosing in which table to place the link field
Good morning everyone, happy Easter first of all and thanks for reading my request:
for example I have two tables you want to link with a "relationship" field. Considering that if I put the field in the first or second table it would seem to work correctly, I would like to understand with what criterion I should put the relationship field in one or the other to do something correct.
Ciao Fabio,
Using an example (see attached) with tables of 'customers', 'customer invoices', 'customer addresses', industry types, size, there are a couple of guidelines that you can use almost all of the time.
1. The rule of 'one to many' and 'ownership'
1a
Can a customer have many invoices - yes, a customer can have many invoices
Can an invoice have many customers - no, an invoice can only have one customer
Therefore you set the relationship on the 'many' table - in this example, the 'invoice' table and you can consider the invoice as being 'owned by' the customer.
1b
Can a customer have many addresses - yes, a customer may have addresses in different cities.
Can an address have many customers - no, an address can only have one customer.
Therefore you set the relationship on the 'many' table - in this example, the 'address' table and you can consider the address as being 'owned by' the customer.
1c
There is invariably a top-down hierarchy in these sorts of relationships (start with 'customers' and work down).
.
2. The rule of 'attribution' (or 'description').
In this type of relationship, you select values from related tables that are attributes of your customer and help to describe them: for example:
2a
The values in the 'industry types' table are a way of describing what industry your customer operates in (construction, transport etc) but you would not consider your customer to be 'owned by' the industry types. So you set the relationship on the table of the thing you want to describe (in this case, the 'customer');
2b
The values in the 'size' table are a way of describing the size of your customer (local, national, international etc) but you would not consider your customer to be 'owned' by the 'size'. So you set the relationship on the table of the thing you want to describe (in this case, the 'customer');
2c
You will frequently find that a table that has only one field is an 'attribution' table (perhaps it helps to think about it as a simple 'pick list').
2d
There is rarely a 'top down' hierarchy in these sorts of relationships - they are typically 'flat'.
The terminology I use is my own (you certainly wouldn't find it in any text books) and I do have concerns (especially when you translate this post into different languages) that it might confuse more than it clarifies.
And obviously there are always exceptions.
Finally, I would say that if you find you are having to write lots of code to 'fix' a problem, it usually means that your database design is not as it should be!
But as a basis on which to design your database schema, hopefully this helps you understand things better.
db design.zip 360.92 kb, 82 downloads since 2023-04-09
Hi Fabio, Derek
Let's try to simplify. I think that this will already give an understanding of what Derek wrote.
An excerpt from the book "Ben Forta - Sams Teach Yourself SQL in 10 Minutes".
The best way to understand relational tables is to look at a real-world example.
Suppose you had a database table containing a product catalog, with each catalog item in its own row. The kind of information you would store with each item would include a product description and price, along with vendor information about the company that creates the product.
Now suppose that you had multiple catalog items created by the same vendor. Where would you store the vendor information (things like vendor name, address, and contact information)? You wouldn’t want to store that data along with the products for several reasons:
• Because the vendor information is the same for each product that vendor produces, repeating the information for each product is a waste of time and storage space.
• If vendor information changes (for example, if the vendor moves or his area code changes), you would need to update every occurrence of the vendor information.
• When data is repeated, (that is, the vendor information is used with each product), there is a high likelihood that the data will not be entered exactly the same way each time. Inconsistent data is extremely difficult to use in reporting.
The key here is that having multiple occurrences of the same data is never a good thing, and that principle is the basis for relational database design. Relational tables are designed so that information is split into multiple tables, one for each data type.
The tables are related to each other through common values (and thus the relational in relational design).
In our example, you can create two tables, one for vendor information and one for product information. The Vendors table contains all the vendor information, one table row per vendor, along with a unique identifier for each vendor. This value, called a primary key , can be a vendor ID, or any other unique value.
The Products table stores only product information, and no vendor specific information other than the vendor ID (the Vendors table’s primary key). This key relates the Vendors table to the Products table, and using this vendor ID enables you to use the Vendors table to find the details about the appropriate vendor.
What does this do for you? Well, consider the following:
• Vendor information is never repeated, and so time and space are not wasted.
• If vendor information changes, you can update a single record, the one in the Vendors table. Data in related tables does not change.
• As no data is repeated, the data used is obviously consistent, making data reporting and manipulation much simpler.
Hi derek and sparrow. Wow you took a long time to answer me and I am infinitely grateful. In any case, you didn't waste time in vain because I understood the concept of reletionships well. I really thank you very much.
My Visual Database → General → Criteria for choosing in which table to place the link field
Powered by PunBB, supported by Informer Technologies, Inc.
Theme Hydrogen by Kushi