The scenario is two products that share the same user/client/group information but have no other common data, is it best practice to have a single database or two databases where the user/client/group information is regularly synced from one to the other? ...
Which situation is better? Assuming upwards of 300 possible values on what would essentially be a table of key/value pairs where the column in question is the key: Enum or Char/Varchar I'm just wondering if anyone has dealt with an Enum column with that many possible values and what the pitfalls might be. EDIT: Keys ARE predefined. ...
To describe my dilemma, let me first start with an example problem (stolen from here). Let's say you have a GradStudent table in your database that looks like this: GradStudent: firstName lastName birthDate courseAssignment researchGrant But only teaching assistants will have a course assignment and only research assistants will have ...
I'm attempting to model a grocery store. In the store, there are several "aisles". Each "aisle" has a group of "categories" of "items" it stores. Each "category" can only belong to one "aisle". Each "item" can only have one "category". The data model seems straight forward to me: An "aisle" table with an ID and DESCRIPTION A...
I have two tables users and lead_contacts which have similar data. When somebody buys a product they become a user. How should I modify the two create staements below so: that the leads table receives a new entry with first_name, last_name, company and email, when a user is created. the first_name, last_name, company and email in use...
Let's say I have books which can be romance, fiction, or mystery. I have 2 realistic options to store this data. One is to have a type column in my books table that is a string with the value "romance", "fiction", or "mystery". The other is to create a book_types table and store the types in there. Then my books would have a type_id ...
I have a shopping cart in which I have to keep track of stock, and this includes stock with attributes. For example: shirt -- blue, small = 50 -- blue, medium = 22 -- blue, large = 53 -- red, small = 15 etc... These fields are currently separated by commas in the database and the IDs would be as follows: 1,3 1,4 1,5 2,3 wh...
I have always wondered how Facebook designed the friend <-> user relation. I figure the user table is something like this: user_email PK user_id PK password I figure the table with user's data (sex, age etc connected via user email I would assume). How does it connect all the friends to this user? Something like this? user_id...
I want to create an indexed view that integrates data from several tables, however SQL Server complains with the error message: Cannot create index on view "MyView". The view contains a self join on "dbo.Companies". The view definition is something like this (simplified version): SELECT T.Field1 , T.Field2 , P.CompanyNam...
All, Looking for some guidance on an Oracle design decision I am currently trying to evaluate: The problem I have data in three separate schemas on the same oracle db server. I am looking to build an application that will show data from all three schemas, however the data that is shown will be based on real time sorting and prioritis...
How to design database and organize data if I have two or more domains and one database? All sites for e-commerce purpose and one good can be for sale on each site. So I have only two ideas: I need to create one more field (site_id) in almost every table and duplicate data. I need to create one table with site_id information for all ot...
I'm trying to study and I can't find a good explanation of this. And my professor's example is really bad. thank you! ...
I'm looking at this data model I've come up with and not feeling comfortable. I've changed the entity names so it (hopefully) makes more sense. In any event, how would you model the following? I have 3 entities. GovernmentCustomer, PrivateCustomer, PublicCustomer. Private and Public Customer are both CorporateCustomers. Corporate and Go...
In my brand new data warehouse that is built (of course) from the OLTP database, I have dropped all the IDENTITY columns and changed them to INT columns. What are the best practices regarding the following especially since the warehouse is denormalized: Primary Key -> this may now be a composite key because several tables have come t...
Hello SO Gus, I'm working on project that lets users choose some scientific authors and columnists and track their activities. Users can track authors activities either by : Choosing the author explicitly and then tracking him Or Choosing a channel (a list of authors that some other user tracks) and track the entire channel He...
I'm writing a game that is played in a pseudo-Internet like environment. I'm just doing the "DNS" to the game. Basically, a database which links strings ("URLs") to machines (another entity in the database). Pretty much like in real life: Each machine can have zero or more urls Each url has a tld. For simplicity, there are only TLDs a...
I am designing an Org Chart, model is almost ready and simplified a bit for clarity here. OrgUnit (OrgUnitId, Name, ReportsToOrgUnitId, ...) OrgUnitJobs (OrgUnitJobId, OrgUnitId, JobName, ReportsToOrgUnitJobId, ... ,IsJobGroup) Employee (EmployeeId, ........) OrgUnitJobEmployee (OrgUnitJobId, EmployeeId, AssignedDate, .....,) so I wan...
I need to create a database structure for managing a gantt style system of tasks and resources, there are some custom needs and the technology involved doesn't play well with others, so I'm thinking of doing it from scratch. My needs are simple: Tasks, prioritized Resources (just people) Calendars (mostly being able to handle non-worki...
How you can see above, it's a kind of subclass a table in a RDBMS (in this case, my favorite: MySQL) so I handle it with Visual Subclassing a base form for tb_order_base with validating field data, etc. This way, I'm free of repeating code and some other bothering problems, well, it seems a true OO aproach. But ... Now I got a Big pr...
As a junior programmer I think it may be beneficial to learn solid database design. What books would you recommend on learning database design? Just to be clear, the specific aspect of design I am referring to is creating a collection of tables and their relationships. Note: The programming books I have enjoyed reading (gaged by teachin...