Hi ! I have a very simple question for a beginner in SQL. I have defined 3 tables in my database : "DOCTOR", "PATIENTS", "VISITS". For simplicity purposes, I have only a one-to-many relationship btw these tables : One doctor has many patients, but a patient can see only one doctor and one patient can make many visits... in my table "VISI...
I haven't been confronted with this yet, but this is what i think (very superficial and simplistic imho) If you have a key value kind of storage and all you accesses are key lookups use the NOSQL solutions. If you want lookups based on values (and subvalues) or have something more complicated like joins you would go for a relational sol...
Could someone help me with a rough database schema for a timesheet application where the i would be able to Store hours per day for a time period ( 2 weeks ) for different projects. Ex person A can put 3 hours for projectA and 4 hours for projectB on the same day Make it so that its is easy to get a reports on total hours put for a pro...
I'm new to database design and I have been reading quite a bit about normalization. If I had three tables: Accommodation, Train Stations and Airports. Would I have address columns in each table or an address table that is referenced by the other tables? Is there such a thing as over-normalization? Thanks ...
A dairy farmer, who is also a part-time cartoonist, has several herds of cows. He has assigned each cow to a particular herd. In each herd, the farmer has one cow that is his favorite - often that cow is featured in a cartoon. A few malcontents in each herd, mainly those who feel they should have appeared in the cartoon, disagree with t...
Hi, I am currently building a small web application for publishing stories using PHP and MySQL. Each story may either be a article (with lots of fields and data), just a hyperlink/url, just an image, or a downloadable file with certain attributes (once again with lots of fields). My problem is related to finding the optimal database la...
For you database design/performance gurus out there. I'm designing a table, I have the choice of either use int or nvarchar (128) for a column, assume space is not a problem. My question is which will give performance when I search with int column where ID = 12324 or when I search with nvarchar column (the Key is the entire value, ...
I'm learning about the usage of datatypes for databases. For example: Which is better for email? varchar[100], char[100], or tinyint (joking) Which is better for username? should I use int, bigint, or varchar? Explain. Some of my friends say that if we use int, bigint, or another numeric datatype it will be better (facebook does it). ...
Hi, I am in process of designing my CMS that I am about to create. I was thinking about the database and how I want to go by approaching it. Do you think its best to create 1 master database for all my clients websites? or Should I have 1 database per site? What is the benefits and negatives on both approaches? I am always thinking abo...
Hi, I have a table in Oracle DB,say, Student table. StudentID is the primary key in the table.I have another column interested subjects,say columns name is interested_SUB. A student can have more than one interested subject. In this case, I have the following 2 options: 1) Having the StudentID and Interested_SUB columns as the composite...
I would like to build a database of pictures. Each picture may have 1 or more tags, for example: Paris, April 2010, David. How would you store this information ? I thought to have a Files table with 1 row per file, and one of the columns would be Tags IDs separated by commas, for example: 2,4,14,15 In other table called Tags I thought ...
Hello. I have read other answers on this (or at least near to this) subject but I couldn't get a clear view of it so I'm asking for help again. I have a complex dynamic HTML form that I would like to submit to database using PHP. The form is split into multiple tabs and in each tab I got checkboxes that trigger other parts of the form. ...
i am using a simple database design and i think the best database example is e-commerce, because it does have a lot of problems, and its familiar to cms. USERS TABLE UID |int | PK NN UN AI username |varchar(45) | UQ INDEX password |varchar(100) | 100 varchar for $6$rounds=5000$ cr...
I'm implementing a Stackoverflow-like reputation system on my rap lyrics explanation site, Rap Genius: Good explanation: +10 Bad explanation: -1 Have the most explanations on a song: +30 My question is how to implement this. Specifically, I'm trying to decide whether I should create a table of reputation_events to aid in reputation r...
I recently became involved with a new software project which uses SQL Server 2000 for its data storage. In reviewing the project, I discovered that one of the main tables uses a clustered index on its primary key which consists of four columns: Sequence numeric(18, 0) Date datetime Client varchar(9) Hash tinyint This ta...
Which is more expensive to do in terms of resources and efficiency, File read/write operation or Database Read/Write operation ...
Boiled down: Is it better to have the calculated field returned with the rest of the results or is it better to calculate it later when you need it? Example: I have a GridView that displays search results that has many fields like: Application ID Name Account Type Account Number etc. The account number field is not always consisten...
If I have a relationship between two tables (both tables have their own primary keys) what should guide my decision as to which table should store the foreign key? I understand that the nature of the relationship probably matters (one-to-one, one-to-many, many-to-many, uni-directional, bi-directional), and probably access patterns matter...
15 ECTS credits worth of database design down the bin.. I really can't come up with the best design solution for my problem. Which is this: Basically I'm making a tool that gathers a lot of information concerning the user. At the most the user would fill in 50 fields of data, ranging from simple checkboxes to text input. I'm designing t...
I am building a web application that will essentially allow authenticated users access to mass amounts of data, but I don't want users to only have read-only access. If there are records missing fields but a user has found information to fill these fields or correct already populated data, I would like the user to be able to do so. Howe...