A few questions about database design
If I have a customer table, it will store name, address, email address, phone number, and maybe even some information about the customer like age, preferences, etc.
Will I perform well if I break this down into smaller tables? For example. customer_contact with contact fields and leave only name, date of birth, etc. in the original Customer table.
Also, with lookup tables, they are a combination of fields from separate tables into one big table, right?
Also, on my own systems, I have a table representing the product, but all it has is an ID. The only field in this table is a field / attribute that applies to many products (for example if it is a traffic law) and this is a field in another table, so there is a constraint between both tables (relationship). I would guess that the lookup table joins these two tables, right?
thank
source to share
In most cases it is usually better to decompose. Of course, in the case of everything you mentioned.
Think about your database design like OOP program in Java language where complex objects are related. Anything that can be "linked" to your object, especially if it can be linked to multiple objects, can be a candidate object, and therefore a table.
Give the master table of clients only the basic information about the individual that is needed to identify him as you intended.
Then all other metadata and ancillary data can be bound to it. For example, addresses or phone numbers or emails are good candidates for objects, deserve their own table, especially since they may have additional properties. Another table can then associate addresses with customers (for example, what if you have a whole family using your system).
source to share
I think database design is about balance and judgment. If you don't see a large database, then normalize it. If you see it getting pretty big, then IMHO is holding back normalization, unless its necessary IE uses collation tables everywhere, no matter what someone says the smoothed database is faster.
I would keep the address in the same table unless you feel there is a chance the customer wants a history of addresses or a separate billing and shipping address. I would never rip up the contact details and the birthday, because theirs is not really a problem.
I use lookup tables like enums and in fact most of them become Enums.
Everyone has their own ideas about database design ...
source to share
You are asking the right questions. The concept of dividing your data into reusable tables is called "normalization". Typical customer relationship management (CRM) systems have some tables like Phone, Address, Person ... very general tables that can be reused for different purposes.
For example, Phone and Address can be used not only for customers, but also for shippers, suppliers, employees, etc.
Once you have the basic structure in place, you can start associating customers with addresses and phone numbers. And remember, every customer can have a ShippingAddress, BillingAddress, HomePhone, BillingPhone, MobilePhone, etc. You will create tables such as CustomerAddress and CustomerPhone to match customers with their respective information.
source to share
Generally speaking, you would only (vertically) partition tables, as if you had a HUGE number of columns (in which case you would probably be redesigned) or if you have different security requirements for different data (SSN or salary data separated from normal data).
When you say "lookup table" I think you are actually referring to "foreign keys". If you have a table that contains the availability of a product, then each row will have a ProductID that points to all other product information.
source to share
The term lookup table is used primarily. If you think about standard programming again, the lookup table is equivalent to using constants to denote magic numbers or constant objects.
Hence, you are using a unique identifier for another object, which is usually atomic because it does not contain other objects (such as state, address, product information, etc.). In the main table, you will have an ID, not actual data.
If the table refers to the central entity, it is best to think in terms of relationships rather than lookup tables.
source to share
It's a bit of a balancing act, having all your columns in the same table (denormalized) will result in fewer joins and better performance, but will be painful to maintain if you have to change things later. As Uri said , thinking about what your database design from an OOP perspective will help you determine which tables should be independent. I highly recommend learning how to make a simple entity-entity diagram . This will allow you to define the structure of the database and figure out how things will relate to each other before getting too carried away with the implementation.
source to share
Someone much wiser than me once said, "Normalize until it hurts, denormalize until it works."
It is advisable to break the long table into smaller semantic chunks that connect through one-to-one relationships. Then you can call them through the view. Even many ORMs are View Friendly
However, these additional connections will harm you if your database has a lot of hits, such as in a network or intranet scenario.
If you want your tables to be partitioned in a high stress scenario, you can use a dirty and ugly cheat that is used in many public web projects and create a fake view by delegating a column in the master table to store related data in the collection ...
source to share