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 ...


9

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 ...


9

I am aware that it has been some time since the OP asked this question, but I hope this answer can be useful for other seekers with a similar problem. If I understand the situation well, the part of the scenario that you are confused with is an example of what is called (super)type-subtype[1] relationship. Context This kind of relationship shows up when, ...


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 ...


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

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 ...


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

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, ...


6

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 ...


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

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

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

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

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. ...


5

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 ...


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

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

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

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 ...


4

I think you are trying to blend together concepts from object modeling and concepts from data modeling in a way that isn't helping you to clarify your own understanding of the problem. I hope I can clear the clutter a little without too much rambling. The relational model, as such, does not support inheritance, never mind polymorphism. This means that a ...


4

No, you shouldn't merge the 3 tables. For a given flight, aircraft may change For a given aircraft, flight may change However, I'd suggest that your model isn't complex enough FlightSeats depends on Aircraft model (seat layout etc) Aircraft departs Airport too... .. but Flights leave and departs Airports. Or is it FlightAirport? Flight is arguably 2 ...


4

City ---- CityID CityName PRIMARY KEY (CityID) Hotel ----- HotelID HotelName CityID PRIMARY KEY (HotelID) FOREIGN KEY (CityID) REFERENCES City (CityID) Package ------- PackageID PackageName CityID PRIMARY KEY (PackageID) UNIQUE KEY (PackageName) FOREIGN KEY (CityID) REFERENCES City (CityID) Package versions: PackageVersion -------------- PackageID ...


4

There's an open source project in SourceForge called Schema Spy (http://schemaspy.sourceforge.net/) which generates HTML schema reports, including ERDs: http://schemaspy.sourceforge.net/unifieddb/mangos/relationships.html On the good side this tool is free, however the diagrams are not editable.


4

I agree with Spredzy's answer but would add that as a matter of preference I would use camelCase instead of under_score. firstName, lastName etc.


4

Here's my idea: ResourceType: -- 2 rows: hotel, city --------- ResourceTypeID not null autoincrement primary key ResourceType not null unique key Resource: -- both hotels & cities --------- ResourceID not null autoincrement primary key ResourceTypeID not null foreign key references ResourceType (ResourceTypeID) Name not null unique --can repeat the ...



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