Tag Info

Hot answers tagged

30

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


11

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


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

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

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


5

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


5

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.


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

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


4

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


4

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


4

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


4

There are two ways to look at this, the informal way and the formal, theoretical way. The Informal Way: If something is of so much interest to your system that you want to record attributes about it, then it probably belongs in its own table. If the only thing you care about Social Security Number is what digits it contains (i.e. what the number is) then ...


4

One rule of thumb is that if it is 'physical' (concrete as opposed to abstract) then it is an entity otherwise it is an attribute. Another rule of thumb in ER modelling is that an table EITHER models an entity OR a relationship between entities but never both. There can be other kinds of table, of course e.g. it is sometimes conducive to put all attributes ...


4

First, 'Friend' is probably not a different entity but just a relationship from 'User' to 'User' but your case may be special, I don't know. So about "if I add a friendID, should I have it as a derived key from user", assuming a friend is just another user - yes. Additional info like 'last voice chat' & 'last im' shouldn't be part of a composite ...


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


4

Oracle has a free tool SQL Developer that has can presumably reverse engineer an Oracle database. If the database isn't too large then you could possibly use a tool like DbVisualizer (which is either free or low cost depending on the version). You can only diagram one schema at a time and results aren't directly editible (it's really more of an exploration ...


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


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

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

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


3

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


3

Is "resort" a special type of entity? If not then you could model this as: Customer -------- CustomerID - unique primary key Name - text (other customer fields) Accomodation ------------ AccomodationID - unique primary key LocationID - foreign key to "where" the accomodation is AccomodationTypeID - foreign key to types of accomodations ...


3

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


3

ERD will save you much more time than you think, If you do everything on the fly then you will stuck when you want to generate junction tables. If you come across to database a few years later without ERD, you have to spend a lot of time to see what is going on in your project. I have company and we design ERD, don't ever think about database without ...


3

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


3

Your first option works best when there is the possibility that a lecture can have multiple tutors or when a tutor can teach multiple classes. Your second option works best when you can guarantee that a class will only ever have a single tutor at any one time (otherwise you would need multiple entries in your class table to accommodate extra tutors). The ...


3

I know of ERWin, though we don't use it at our company. We tend to "bleed blue" (ie, use IBM), so we use InfoSphere Data Architect. Ideas for paid products anyway.... You could also check out IBM Data Studio as its free. I believe it does ER to DDL and it supports more than just IBM databases.



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