Noob. The duplication and flattening of information without going down a rabbit hole.

[akedm]

Hi,


I get tripped up at the very beginning of design because I often hear contradictory methodologies in the same paragraph. Or rather I hear a methodology, but the presenters and authors continually forget to address the most basic of questions, which is degree: How far is too far? How much design is too much?




What I hear is don't make flat files (that is, a file where a single table contains too much information that should instead be split up). But what authors forget to address is that flat is relative. So, their examples flatten a lot of information. But why that amount of information? Is it flattened too much, or not enough?


Going hand-in-hand with flattening is do not to duplicate information. But guess what? But guess what - that makes the duplication of information also relative.


For example, many introductory lessons are about making a database for inventory control, or an invoicing system, or some other database where blank has blank. That's the basic of it there: Blank has blank. (eg, these people have some combination of those things, these branch offices have some combination of those employees, these employees do some combination of those duties, these duties require some combination of those educational courses, these educational courses ... and so on).


Welcome to my rabbit hole.


Invariably, I'm told one of the tables should be the people-table (or customers-table, or office-branches-table, or whatever). And the example fields are almost always: Name, Title, Address, City, State, Postal Code, Phone, Birthdate, and/or other basic demographic stuff, and finally some sort of identifying key.


You might see where I'm going with this: Why is this the right number of fields to put in a table? Why not less? If there's many people within the same city, then why not have the city be a separate table? Because what if they move, and you need to know where they moved from and where they moved to? The only truths I know are: Flat is relative, and duplication is relative. Sure, this table as-is may work great matching against a table of items the person has. But what is I want to include more information about the city these people live in? For example, common suppliers that serve the people within the same city? Shouldn't then City and State be in its own table?


Shouldn't sex and gender be separate tables?


It goes on and on.


Help

[Micron]

Try to think of it in an Entity - Attribute way. Every entity has attributes (you have a height and weight). If the table is about people and those attributes are relative to the purpose of the db, those fields would be in there. If the field is not about one of your characteristics (attribute), it does not belong in the table.

When it comes to history, if you want to keep track of changes (e.g. address changes) then you put addresses in its own table (a junction table) you don't repeat all the other info about the person in the person table.

[akedm]

Ok - ok. That helps. Thank you.

[Micron]

That was a rather simplistic and condensed lesson. What we often find is that it can be difficult deciding whether something is an entity, or an attribute of an entity we have identified. When trying to decide, that "something" might just reveal that you have another entity that you did not foresee. Your thread title is a bit at odds to what I'm saying because duplication of information is seldom a good idea, and neither is the flattening of it.

If you have not researched db normalization I would recommend it.
https://www.accessforums.net/showthr...776#post507776

[akedm]

I'll check that out too. Thank you!

[CJ_London]

I try to think ahead to where things might be in the future.

You start off, you are a small business and you have twenty customers. Each customer has an address. So at this point of time a single table with customer name and address is sufficient. But you expect to expand and down the line you'll acquire new customers. Some of those customers have two addresses, one for delivery and one for the invoice. Now your original design won't work so taking the flat file approach, you add another set of address fields to your table and modify your queries, forms and reports to use them. Time goes on and now you have customers with multiple delivery addresses. So now you need a major redesign to cope with that. And then a customer moves, so you need to change the address. Perhaps you need to keep the old address for some reason (such as needing to reprint a pre move invoice, or modify the cost of delivery based on area) - if you do - how are you going to manage that.

So if your customers are always going to be small (or at least only ever have one address), the 'flat file' approach is fine. But if there is the slightest chance they could have multiple addresses, you need separate tables for customers and addresses - with perhaps another table to indicate the type of address (invoice/delivery etc)

And the example fields are almost always:

point is they are just that - examples

why not have the city be a separate table

In addition to the normalisation methods you are now researching, there are two good pointers to poor design (and poor design will ultimately fail) - using table names as a 'field name' i.e. don't have tables called London, New York, etc, have one table called Cities. The second pointer is repeating field names in a table (e.g. InvAddr1, InvAddr2, DelAddr1, DelAddr2) It''s not the Addr1/Addr2 repeating because the 1/2 reference the address line, It's the Inv/Del being repeated.

[ArviLaanemets]

Let's consider the design, where addresses for customers from different cities are kept in different tables. Now when you get a new customer located a city you didn't have a addresses table for before, you have to add a new table - and to redesign whole DB (adding new forms, new reports, redesigning all total forms/reports, as those read data for all cities, etc.). And you have to repeat all this whenever a next new client from different city arrives. Having all addresses in single table allows you to avoid all this.

And about table sizes - what counts most is the size of database, not the size of tables. And having the same kind of info split to different tables generally results as increase of DB size.

About repeating the same info in different tables. There is an exception which is obligatory - any log files, as those must remain unchanged after the record there is saved.

[CJ_London]

in terms of storing same data in more than one place - there is another example I would call 'legal'. Typically this would be an invoice or order where you as a business are required to retain information for (in the UK) at least) a minimum of 6 years (and in the case of HR data, 40 years). So with an invoice line, you would store most of the data, not just a FK to the the price table (since prices can change), the discount (since discount calculations can change) the tax rate (since these can change as well) etc. IN HR, you would store the actual salary and not just a FK to payscale table, etc

[orange]

akedm,

Further to the info you have been given, I'm recommending a tutorial by Roger Carlson. It will take you from a sample business description and lead you through a process to isolate tables and relationships and proper design to support the sample business.
Download the tutorial. It has instructions, and a solution. It will take you about 30 to 45 minutes to complete. You will learn Normalization and design by experiencing the process. What you learn can be applied to any database.

[akedm]

Thank you everyone! I got busy this weekend trying to figure out how to play the board game Spirit Island. After many YouTube videos I may have a handle on it. Anyway, I took a break from learning Access and RDBMS for a bit. Now I'm back and will check out your suggestions - ALL OF THEM.

I'm going to keep this thread open for a bit if that's ok with the moderators. Maybe close it (solve it) this coming weekend. I'm loving the perspectives and input offered here, and want to keep the opportunity for more input available for now.

Thanks!

[pbaldy]

I'm going to keep this thread open for a bit if that's ok with the moderators.

It's okay with the moderators!