mysql - Database Design: dating-site-like application? - Stack Overflow

Introduction to Database Design

dating database design

Do you have tables with many fields, a limited number of records, and many empty fields in individual records? Does anyone have any advice as to the best way to design this. Therefore, the data to be stored in the database must be determined in cooperation with a person who does have expertise in that domain, and who is aware of what data must be stored within the system. What normalization cannot do is ensure that you have all the correct data items to begin with. If there is a 'many' on the left side, this will be indicated with 'M', if there is a 'many' on the right side it is indicated with 'N'. This data model contains all the needed logical and physical design choices and physical storage parameters needed to generate a design in a data definition language , which can then be used to create a database.

Your Answer

The date is dependent on the sales number, and the quantity is dependent on the sales number and the sold product. Decide what information you want to store in each table. Make adjustments to the design, as needed. In the end, you are much more likely to end up with a database that meets your needs and can easily accommodate change. The next step is to determine the relationships between the entities and to determine the cardinality of each relationship. Presenting Entities and Relationships: Certain principles guide the database design process.

NET interview questions with mostly asked questions in. NET Interview questions and answers. Best article I have ever read, very nicely explained.

Hi, I have just gone thru this video and explanation and I found that it was very useful. Can you please let me know the tools with versions required to implement the same and also the appropriate website to download?

A well written and useful article, although I don't agree with rule Just because the code works, it doesn't mean that it is good code. My vote of 5 Pratik Bhuva Mar I'm totally new to database design and really appreciate your article!

Rule 10 has to be put down opc3 Sep If you're designing a data model with the express purpose to gain simplicity in a. NET application class then you're doing it wrong. If you want to abstract your "code type" table which is a nonsensical idea for an entity to begin with, think that through then abstract the multitude of domain lookup tables you should be creating and maintaining with a "getCodes" stored procedure that unions all the attributes together, however allow for the specialized attributes to be added to the domain lookup tables later, e.

Consider not oversimplifying your data model with conveniences that paint you into the proverbial corner while trying to pass them off as Rules for database design. Rule 10 is a classic anti-pattern. Rule 10 has to be put down Anurag Gandhi Aug 0: Yeah, while reading the article i was thinking that it conflicts my thought process. When I see your comment, i got some more person who thinks like me.

Life is a computer program and everyone is the programmer of his own life. My vote of 5, but one doubt Thava Rajan Feb 0: Thank you very much for your helpful article. Can you tell me which software or tool you use to draw the table in the image? It looks amazing and I like it. My vote of 5 David Days Feb 6: Really good outline of how to approach a project from the database side.

I use many of these same principles, though more from painful experience than having a written guide. Do not be hard on redundancy Jan Zumwalt Nov 7: I appreciate your well thought out topic. I would add that redundancy is not always a bad thing if it makes maintenance clear and and easy. My Vote of 5. My vote of 5 Xavi Rius Sep Great article, a really good job. My vote of 5 Jasmine Sep I agree with Item 10 actually, because I've seen it used to great effectiveness in a real application where maintaining a whole table for each name-value type would have been nearly impossible.

However, I think that 7 could use a little work. You need to drive home the point that calculated columns are ok if they are actually defined that way, rather than calculated and then stored.

Point out the fact that if column X is supposed be the product of Y and Z, if it's possible to store a number in X that is NOT the product of Y an Z, violating your rule, then it will happen at some point and your app will break. Define the column as calculated from those other columns, don't fill it in with a trigger or something. If you can't define it that way and have the DB enforce that definition, you risk the integrity of that column. My vote of 2 Member 7-Sep My vote of 4 brother.

I would give 5 if there were a few hints about how to "fix" an existing database that is replete with these kinds of errors. Also, a brief explanation of terms such as "primary key" and "normal forms" at the top of the article would be very nice.

I give a 4 because the explanation of the database refinements is a worthy topic, the diagrams are pleasant, and the authour is candid. Also, I know Mr. Koirala is from India, but the English could use a little sprucing up! My vote of 4 Uilleam 9-Apr 5: I want to encourage this sort of discussion as these are mostly very good points that frankly all should be at least aware of, and able to argue one way or another.

On the other hand, I too think 10 is really a BAD idea. There are plenty of examples of similar structures but that alone is no reason to merge data - data management is about a lot more than simply structure - it's relationships, and for example there are plenty of countries that share currency with others e.

If you must merge all country data then a better way would be a table of countries having fields of country code, currency code, and currency name, and then provide indices on both code fields. That at least provides the benefit that you shouldn't have a country added without both codes provided, but frankly I'd make them separate tables at least for the shared currency issue. My vote of 4 Shivprasad koirala Apr 0: In the ERD the primary key attributes are indicated by the text 'PK' behind the name of the attribute.

In the example only the entity 'shop' does not have an obvious candidate for the PK, so we will introduce a new attribute for that entity: The Foreign Key FK in an entity is the reference to the primary key of another entity. The foreign key of an entity can also be part of the primary key, in that case the attribute will be indicated with 'PF' behind its name.

This is usually the case with the link-entities, because you usually link two instances only once together with 1 sale only 1 product type is sold 1 time. Please note that the attribute 'products' is no longer necessary in 'Sales', because 'sold products' is now included in the link-table.

In the link-table another field was added, 'quantity', that indicates how many products were sold. The quantity field was also added in the stock-table, to indicate how many products are still in store. Primary keys and foreign keys. Now it is time to figure out which data types need to be used for the attributes.

There are a lot of different data types. A few are standardized, but many databases have their own data types that all have their own advantages. Some databases offerthe possibility to define your own data types, in case the standard types cannot do the things you need.

The standard data types that every database knows, and are most-used, are: CHAR length - includes text characters, numbers, punctuations CHAR has as characteristic that it always saves a fixed amount of positions. If you define a CHAR 10 you can save up to ten positions maximum, but if you only use two positions the database will still save 10 positions. The remaining eight positions will be filled by spaces.

TEXT - can contain large amounts of text. Depending on the type of database this can add up to gigabytes. INT - contains a positive or negative whole number. These variations differ from the INT only in the size of the figure that fits into it. Do note that this does not always work perfectly. BLOB - for binary data such as files. Also useable for netmasks. Data model displaying data types. Normalization makes your data model flexible and reliable. It does generate some overhead because you usually get more tables, but it enables you to do many things with your data model without having to adjust it.

The first form of normalization states that there may be no repeating groups of columns in an entity. We could have created an entity 'sales' with attributes for each of the products that were bought.

This would look like this:. Not in 1st normal form. What is wrong about this is that now only 3 products can be sold. If you would have to sell 4 products, than you would have to start a second sale or adjust your data model by adding 'product4' attributes.

Both solutions are unwanted. In these cases you should always create a new entity that you link to the old one via a one-to-many relationship. In accordance with 1st normal form. The second form of normalization states that all attributes of an entity should be fully dependent on the whole primary key. This means that each attribute of an entity can only be identified through the whole primary key. Not in 2nd normal form. This entity is not according the second normalization form, because in order to be able to look up the date of a sale, I do not have to know what is sold productnr , the only thing I need to know is the sales number.

In accordance with 2nd normal form. Now each attribute of the entities is dependent on the whole PK of the entity. The date is dependent on the sales number, and the quantity is dependent on the sales number and the sold product. The third form of normalization states that all attributes need to be directly dependent on the primary key, and not on other attributes. This seems to be what the second form of normalization states, but in the second form is actually stated the opposite.

In the second form of normalization you point out attributes through the PK, in the third form of normalization every attribute needs to be dependent on the PK, and nothing else. Not in 3rd normal form. In this case the price of a loose product is dependent on the ordering number, and the ordering number is dependent on the product number and the sales number.

This is not according to the third form of normalization. Again, splitting up the tables solves this. In accordance with 3rd normal form. There are more normalization forms than the three forms mentioned above, but those are not of great interest for the average user.

These other forms are highly specialized for certain applications. If you stick to the design rules and the normalization mentioned in this article, you will create a design that works great for most applications.

If you apply the normalization rules, you will find that the 'manufacturer' in de product table should also be a separate table:. Data model in accordance with 1st, 2nd and 3d normal form.

Attributes - detailed data about an entity, such as price, length, name Cardinality - the relationship between two entities, in figures. For example, a person can place multiple orders. Entities - abstract data that you save in a database. Foreign key FK - a referral to the Primary Key of another table. Foreign Key-columns can only contain values that exist in the Primary Key column that they refer to. Key - a key is used to point out records. The most well-known key is the Primary Key see Primary Key.

Normalization - A flexible data model needs to follow certain rules. Applying these rules is called normalizing. Primary key - one or more columns within a table that together form a unique combination of values by which each record can be pointed out separately. Identifying Entities The types of information that are saved in the database are called 'entities'. Identifying Relationships The next step is to determine the relationships between the entities and to determine the cardinality of each relationship.

To make this easy to do, we'll adjust the notation a bit, by noting the 'backward'-relationship the other way around: Customers Cardinality exists in four types: Attributes in ER diagrams are usually modeled as an oval with the name of the attribute, linked to the entity or relationship that contains the attribute. In the field of relational database design, normalization is a systematic way of ensuring that a database structure is suitable for general-purpose querying and free of certain undesirable characteristics—insertion, update, and deletion anomalies that could lead to loss of data integrity.

A standard piece of database design guidance is that the designer should create a fully normalized design; selective denormalization can subsequently be performed, but only for performance reasons. However, some modeling disciplines, such as the dimensional modeling approach to data warehouse design, explicitly recommend non-normalized designs, i. Schema refinement of the database specifies that the data is normalized to reduce data insufficiency and conflicts.

The physical design of the database specifies the physical configuration of the database on the storage media. This includes detailed specification of data elements , data types , indexing options and other parameters residing in the DBMS data dictionary.

From Wikipedia, the free encyclopedia. Usually, the designer must: Determine the data to be stored in the database. Determine the relationships between the different data elements. Superimpose a logical structure upon the data on the basis of these relationships. Principles and practice using MySQL. Logical Design , 4th edition, Morgan Kaufmann Press. Retrieved May 1, , from https: Data definition Data manipulation Query information retrieval. Activity monitoring Audit Forensics Negative database.

Entities and relationships and Enhanced notation Normalization Refactoring. Abstraction layer Object-relational mapping. Virtualization Tuning caching Migration Preservation Integrity. Administration and automation Query optimization Replication. Automotive design Automotive suspension design CMF design Corrugated box design Electric guitar design Furniture design Sustainable Hardware interface design Motorcycle design Packaging and labeling Photographic lens design Product design Production design Sensory design Service design.

Algorithm design Boiler design Conceptual design Database design Drug design Electrical system design Experimental design Filter design Job design Integrated circuit design Circuit design Physical design Power network design Mechanism design Nuclear weapon design Nucleic acid design Organization design Process design Processor design Protein design Research design Social design Software design Spacecraft design Strategic design Systems design.

Iamges: dating database design

dating database design

It will be updated to ONE as soon as the message is read by the user. Customers The true cardinality can be calculated through assigning the biggest values for left and right, for which 'N' or 'M' are greater than '1'.

dating database design

Normalization - A flexible data model needs to follow certain rules. The term database design can be used to describe many different parts of the design of an overall database system. For such kinds of situations a dimension and fact design is a better approach.

dating database design

In the example only the entity 'shop' does not dating database design an obvious candidate for the PK, so we will introduce a new attribute for that entity: Creating the table dafing. Could anyone give me some advice on any of the above. Because each record contains facts about a product, as well as facts about a supplier, you cannot delete one without deleting the other. Edited 8 Years Ago by nav33n: In desigj case, products are your dating spot fox purchased through a sale, so the relationships dating database design Products' can be deleted. One of the dating database design of violation of this rule is if your queries are using too many string parsing functions like substring, charindex, etc.