Hot answers tagged

31

In your case, the prefix user is redundant. We (the devs in charge) know that this is the table user, so why add user_ prefix in front of every field? What I would suggest to you is to do it with a more natural approach. What are the characteristics of a Person: Last Name, First Name, Birthdate, Nationality, etc... What are the characteristics of a Car: ...


17

In addition to Spredzy's comment, label your primary keys the same ( ID ) so that when you're writing queries on the fly, you can easily recall (u.ID=c.ID) instead of having to look up "Was it countryID, country_ID, countries_ID, countriesID, ?"


14

It is great that you are taking the time to understand, classify and model the data you are dealing with since, from my personal experiencie, all this makes the whole development process easier and very flexible for future changes. And I am quite sure that you are also aware of this already. Preliminary data model and assumed business rules I defined a ...


12

Plain and simple, think of developing a database without an ERD as building a house without a building plan. It might be doable because you think that simply laying a brick one over another is enough to build something, however the moment somebody else takes responsibility over the project there is disaster potential. In my experience you will not benefit ...


11

The part of the scenario that you are confused with is an example of a supertype-subtype[1] relationship. Introduction A relationship of this nature shows up when, in a given circumstance, there is a group of entity types within which the supertype has one or more attributes that are shared by the rest of the entity types in the group, i.e., the subtypes. ...


10

An ER diagram is merely a tool to aid you in development. An ER diagram doesn't even by mandate require you to show all the entities in the system. So to answer your question by one route: It's perfectly acceptable to have an entity on a diagram that has no relationship to any other entity. Consider a settings table that is used by a simple application to ...


10

In SSMS you can easily create diagrams showing the relationships between tables. The basic steps are: Connect to the database server Expand the Databases folder Expand the folder for the database you're investigating. Right click on the Database Diagrams folder. a. Select Install Diagram Support if needed Select New Database Diagram In the wizard add any ...


8

All you need is configured ODBC connection to Oracle DB 1. Install Oracle Client SW (download from otn.oracle.com). I recommend installing same version as your Oracle DB 2. Define connection in tnsnames.ora 3. Create and test ODBC connection to Oracle DB in Control Panel > Administrative Tools > Data Sources (ODBC) - use Oracle's driver in your ORA_HOME, ...


8

I couldn't agree more with David Hall's addendum to Spredzy's excellent answer. Simple and natural is the way to go. Table confusion should not be an issue if you name tables naturally too. No sense having users.user_id and cars.car_id when you could have users.id and cars.id


7

Let's see, with your example it will look something like this: USERS ---- id username, password registration_date I use the table name in uppercase. This lets me identify the table easily. The columns I just named is each for what it represents. I try not to use numbers or include any prefix or suffix with it. This will makes the queries dead simple and ...


7

I believe he is going for something like a binary tree. I would just include three keys that are tied to the unique id of the same table, one for the left, one for the right child, and one for the parent. i.e.- (very much pseudocode) TABLE tree int id autoinc varchar(16) data_you_care_about int parent_id int ...


7

They don't tend to be shown in ERDs. An ERD focuses on the Entities and their Relationships, but an index is a copy of the data from one (or potentially more) of the entities, created to assist in the execution of queries. While it's possible that an index could be unique and therefore contribute to the database design, they are not typically shown on ERDs. ...


7

The General Advice: When you are starting off learning how to model databases, one of the most important rules of thumb is: Every tangible thing that matters to your system is probably an entity type. This is a really good place to start with any logical database design. If you spend some time up front thinking about what kind of things matter to your ...


7

One can say that a proper entity-relationship diagram is that which was created using the constructs introduced by Dr. P. P. Chen (e.g., rectangular and diamond-shaped boxes) in his famous 1976 paper entitled The Entity-Relationship Model—Toward a Unified View of Data. Regarding your specific comparison, the illustration displayed in Figure 1 looks much ...


6

Have you got access to Microsoft Visio? If you can get an ODBC connection to your Oracle DB then you can use Visio to scan the database. It will display all the entities, relationships, etc. Have a look at this on Stack Exchange http://stackoverflow.com/questions/4891387/how-can-i-import-the-contents-of-an-oracle-database-into-visio-to-create-an-enti ...


6

I would argue that in a database schema, every column should have a unique name, across tables. There are several reasons for that: From a modeling point of view: You start with a soup of attributes and you normalize it down into tables. Over time, you might denormalize or normalize further or introduce views or materialized views, or introduce new ...


6

If each node is truly the same data entity, then the paradigm would still signify one table per entity, and a linking column for the tree traversal where each node is only linked once. For entities that are linked at multiple points in the tree, a separate linking table or a multiple distinct value column would be used.


6

This can easily be done by using cascading keys that overlap. Here is an example using the Oracle Data Modeler (note there is a bug in this tool or a configuration issue as the Provider_Feature table should show each column as PF meaning both PK and FK): In this example, the PK to Provider Product includes the Product Number provided, and the PK to ...


6

As per my understanding of your specifications, your scenario involves a ternary relationship. In this regard, you need to define (a) the relationship between a Person and a Survey, (b) the relationship between a Survey and a Question, and (c) the entity type that produces a connection between these two relationships and, as a consequence, between the three ...


5

I'm working in an environment where each column name starts with a prefix derived from the table name, it is not my invention, but I'm quite happy with it. Ideally column names are unique over all tables in the database. Some observations: we only need table aliases, when tables are joined multiple times in a select statement it prevent some faults when ...


5

Like the others, I recommend that you do not include the table name as part of the column. Unless you've got hundreds of tables all with mostly similar column names: if you have multiple dozens of tables all with a column titled ID, then by all means prefix them with the table name. I recently left a company where one of the developers preferred to prefix ...


5

If your tables are already in 5NF then you don't need to think much more about further normalization. Note however that the principle of full normalization (POFN) is no panacea. There may remain non-obvious update anomalies. Database constraints will need to be applied. In my experience, designers who start with an ER diagram are great at implementing ...


5

There is a view object available in several ERD tools that I've seen (TOAD, Agilian, Visio). I'm not entirely sure that it is formally a part of any notation, but could be construed as a stereotype, as it is effectively just another entity. Demonstrating the relationship to the source tables is a bit more difficult, IMO. I would just use a generic ...


5

I think this falls under the category of things whose requirements are so varied and the solutions so complex that there aren't standardized solutions. Ultimately you need these elements: A system for recording the recurrence schedule. A system for generating the recurring events. A system for storing the events generated. You probably need to spend a ...


5

Since you're using MySQL, you might want to take a look at MySQL Workbench. It might not quite display the ERDs you're expecting but it gets the job done. And it does have an import feature. I have only used it to import SQL files but apparently you can import from existing MySQL databases. Also, a long time ago I found the following tools; but were not ...


5

The two solutions model different rules. With the ternary table you are saying that a company may only have certain products in a particular sector. In a different sector there will be a different set of products, though the two sets may overlap, of course. With the binary tables you are stating that sector has no influence over what products a company my ...


5

The purpose of the Author's_University table seems to be to allow one author on an article to be associated with more than one university. There are a couple of potential reasons why that would be helpful: An author might claim multiple universities because they are a professor and/or are associated with more than one university when the article was ...


5

I think you can make an Orders entity that is a relationship between Products and Customers. The Orders would have subclasses FoodOrders and ShoeOrders, which respectively would be the relationships between the subclasses of Food - GroceryStores and Shoes - ShoeStores. ┌───────────┐ ...


4

ERDs used to be more mainstream some time ago. IMO they are more or less optional now. Currently, some highly successful teams do not bother with ERDs at all, yet deliver excellent systems: robust, performant, and easy to maintain in the long run. This is especially true in Agile development, when we start small, with a minimalistic set of tools. ERDs are ...


4

ERDs are really nice to have a clear picture of your database structure. Besides being an important document artefact to you project, it eases when you need to implement queries, especially joins among tables. Imagine how nice it's to have a dual monitor config, on the left side you have you ERD and on the right side your query editor. You can clearly see ...



Only top voted, non community-wiki answers of a minimum length are eligible